In [270]:
# libs
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import os
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import LabelEncoder
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, confusion_matrix, accuracy_score, precision_score, recall_score, f1_score

Preparação dos Dados:¶

1A) Importar os arquivos CSV do dataset¶

In [271]:
# Caminho dos Arquivos
path = 'dataset/'

# Carreguei todos os aquivos um um dicionario de df
arquivos = [a for a in os.listdir(path) if a.endswith('.csv')]
dataframes = {arq.replace('.csv', ''): pd.read_csv(os.path.join(path, arq)) for arq in arquivos}

O que fiz acima foi:¶

  1. Definir uma variável com o caminho para os arquivos csvs
  2. Criei a variável arquivos onde utilizei list comprehension para armazenar todos os arquivos do caminho path que são cvs, caso eu armazene algum arquivo extra na pasta dos datasets que não seja csv, não sera armazenado nessa variável.
  3. Criei um dicionário de dataframes, onde a chave é o nome da tabela e o valor é o próprio dataframe. Exemplo de acesso em um dos df abaixo.
In [272]:
# Visualizando os df
dataframes['olist_customers_dataset']
Out[272]:
customer_id customer_unique_id customer_zip_code_prefix customer_city customer_state
0 06b8999e2fba1a1fbc88172c00ba8bc7 861eff4711a542e4b93843c6dd7febb0 14409 franca SP
1 18955e83d337fd6b2def6b18a428ac77 290c77bc529b7ac935b93aa66c333dc3 9790 sao bernardo do campo SP
2 4e7b3e00288586ebd08712fdd0374a03 060e732b5b29e8181a18229c7b0b2b5e 1151 sao paulo SP
3 b2b6027bc5c5109e529d4dc6358b12c3 259dac757896d24d7702b9acbbff3f3c 8775 mogi das cruzes SP
4 4f2d8ab171c80ec8364f7c12e35b23ad 345ecd01c38d18a9036ed96c73b8d066 13056 campinas SP
... ... ... ... ... ...
99436 17ddf5dd5d51696bb3d7c6291687be6f 1a29b476fee25c95fbafc67c5ac95cf8 3937 sao paulo SP
99437 e7b71a9017aa05c9a7fd292d714858e8 d52a67c98be1cf6a5c84435bd38d095d 6764 taboao da serra SP
99438 5e28dfe12db7fb50a4b2f691faecea5e e9f50caf99f032f0bf3c55141f019d99 60115 fortaleza CE
99439 56b18e2166679b8a959d72dd06da27f9 73c2643a0a458b49f58cea58833b192e 92120 canoas RS
99440 274fa6071e5e17fe303b9748641082c8 84732c5050c01db9b23e19ba39899398 6703 cotia SP

99441 rows × 5 columns

Limpeza dos dados:¶

In [273]:
# Verificando nulos
for tabela, df in dataframes.items():
    print(f"Nulos na tabela: {tabela}")
    print(df.isnull().sum())
    print("---------------" * 10)
Nulos na tabela: olist_order_payments_dataset
order_id                0
payment_sequential      0
payment_type            0
payment_installments    0
payment_value           0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_order_items_dataset
order_id               0
order_item_id          0
product_id             0
seller_id              0
shipping_limit_date    0
price                  0
freight_value          0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_orders_dataset
order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                 160
order_delivered_carrier_date     1783
order_delivered_customer_date    2965
order_estimated_delivery_date       0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: product_category_name_translation
product_category_name            0
product_category_name_english    0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_products_dataset
product_id                      0
product_category_name         610
product_name_lenght           610
product_description_lenght    610
product_photos_qty            610
product_weight_g                2
product_length_cm               2
product_height_cm               2
product_width_cm                2
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_sellers_dataset
seller_id                 0
seller_zip_code_prefix    0
seller_city               0
seller_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_order_reviews_dataset
review_id                      0
order_id                       0
review_score                   0
review_comment_title       87656
review_comment_message     58247
review_creation_date           0
review_answer_timestamp        0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_geolocation_dataset
geolocation_zip_code_prefix    0
geolocation_lat                0
geolocation_lng                0
geolocation_city               0
geolocation_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------
Nulos na tabela: olist_customers_dataset
customer_id                 0
customer_unique_id          0
customer_zip_code_prefix    0
customer_city               0
customer_state              0
dtype: int64
------------------------------------------------------------------------------------------------------------------------------------------------------

Temos nulos em 3 tabelas:¶

  1. olist_orders_dataset
  2. olist_products_dataset
  3. olist_order_reviews_dataset

Vou explorar cada tabela individualmente para identificar o que cada nulo quer dizer

Olist_orders_dataset¶

In [274]:
dataframes['olist_orders_dataset']['order_approved_at']
Out[274]:
0        2017-10-02 11:07:15
1        2018-07-26 03:24:27
2        2018-08-08 08:55:23
3        2017-11-18 19:45:59
4        2018-02-13 22:20:29
                ...         
99436    2017-03-09 09:54:05
99437    2018-02-06 13:10:37
99438    2017-08-27 15:04:16
99439    2018-01-08 21:36:21
99440    2018-03-09 11:20:28
Name: order_approved_at, Length: 99441, dtype: object
In [275]:
dataframes['olist_orders_dataset']['order_delivered_carrier_date']
Out[275]:
0        2017-10-04 19:55:00
1        2018-07-26 14:31:00
2        2018-08-08 13:50:00
3        2017-11-22 13:39:59
4        2018-02-14 19:46:34
                ...         
99436    2017-03-10 11:18:03
99437    2018-02-07 23:22:42
99438    2017-08-28 20:52:26
99439    2018-01-12 15:35:03
99440    2018-03-09 22:11:59
Name: order_delivered_carrier_date, Length: 99441, dtype: object
In [276]:
dataframes['olist_orders_dataset']['order_delivered_customer_date']
Out[276]:
0        2017-10-10 21:25:13
1        2018-08-07 15:27:45
2        2018-08-17 18:06:29
3        2017-12-02 00:28:42
4        2018-02-16 18:17:02
                ...         
99436    2017-03-17 15:08:01
99437    2018-02-28 17:37:56
99438    2017-09-21 11:24:17
99439    2018-01-25 23:32:54
99440    2018-03-16 13:08:30
Name: order_delivered_customer_date, Length: 99441, dtype: object

Cada coluna armazena a data e hora respectiva ao seu nome¶

  1. order_approved_at - Data e Hora que o pedido foi aprovado, se está nulos, temos pedidos que não foram aprovados ou que tiveram algum tipo de erro em sua aprovação
  2. order_delivered_carrier_date: Data e Hora que o pedido foi enviado, se temos nulos pode-se dizer que o pedido não foi enviado ou que teve problemas de envio
  3. order_delivered_customer_date: Data e hora que o pedido foi entregue, se não temos pode-se dizer que o pedido não chegou ao cliente.

Vale ressaltar que as colunas estao em formato object, transformarei as colunas em datetime em todas as tabelas necessárias enquanto faço o tratamento de nulos.

Na primeira coluna, temos apenas 160 dados nulos, como temos um dataset de 100000 dados, 160 dados acaba não sendo tão relevante para nossa análise, dessa forma irei excluir os nulos da tabela order_approved_at

Ja nas 2 tabelas seguintes, temos 1700 e 3000 dados. Ou seja, cerca de 1,7%-3% do dataset completo, tendo em vista que também podemos utilizar esses dados depois para verificar entregas e análises das mesmas farei duas colunas novas sendo elas

  1. "enviados", com valores 1 para pedidos enviados, 0 para pedidos nao enviado(nulos).
  2. "entregues": com valores 1 para os pedidos entregues e 0 para os valores nao entregues(nulos).

E manterei os nulos nas colunas originais.

(Estou escrevendo esse parênteses após ter feito a parte 3 do case. Esses Nulos que eu deixei vão ser impactantes nos modelos que eu tive que fazer naquela parte, como eu ja tinha iniciado o case deixando eles não nulos nesse momento, só vou exclui-los quando estiver na parte 3 do case.)

In [277]:
# Excluir dados da tabela order_approved_at
dataframes['olist_orders_dataset'] = dataframes['olist_orders_dataset'].dropna(subset=['order_approved_at'])

dataframes['olist_orders_dataset']['order_approved_at'].isnull().sum()
Out[277]:
np.int64(0)
In [278]:
# Object para datetime
dataframes['olist_orders_dataset']['order_approved_at'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_approved_at'], errors='coerce')
dataframes['olist_orders_dataset']['order_delivered_carrier_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_delivered_carrier_date'], errors='coerce')
dataframes['olist_orders_dataset']['order_delivered_customer_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_delivered_customer_date'], errors='coerce')
In [279]:
# Criacao de novas colunas
dataframes['olist_orders_dataset']['enviados'] = dataframes['olist_orders_dataset']['order_delivered_carrier_date'].notna().astype(int)
dataframes['olist_orders_dataset']['entregues'] = dataframes['olist_orders_dataset']['order_delivered_customer_date'].notna().astype(int)
In [280]:
dataframes['olist_orders_dataset']['entregues'].value_counts()
Out[280]:
entregues
1    96462
0     2819
Name: count, dtype: int64
In [281]:
dataframes['olist_orders_dataset']['enviados'].value_counts()
Out[281]:
enviados
1    97644
0     1637
Name: count, dtype: int64

olist_products_dataset¶

  1. product_category_name 610
  2. product_name_lenght 610
  3. product_description_lenght 610
  4. product_photos_qty 610
  5. product_weight_g 2
  6. product_length_cm 2
  7. product_height_cm 2
  8. product_width_cm 2

Temos valores muito pequenos para influenciar na análise, e por apresentarem o mesmo valor, provavelmente são referente há um mesmo tipo de produto, tendo isso em vista excluirei, todos os dados nulos dessa tabela:

In [282]:
dataframes['olist_products_dataset'] = dataframes['olist_products_dataset'].dropna(
    subset=['product_category_name', 'product_name_lenght', 'product_description_lenght', 'product_photos_qty'])
In [283]:
dataframes['olist_products_dataset'].isnull().sum()
Out[283]:
product_id                    0
product_category_name         0
product_name_lenght           0
product_description_lenght    0
product_photos_qty            0
product_weight_g              1
product_length_cm             1
product_height_cm             1
product_width_cm              1
dtype: int64
In [284]:
dataframes['olist_products_dataset'].dropna(inplace=True)
In [285]:
dataframes['olist_products_dataset'].isnull().sum()
Out[285]:
product_id                    0
product_category_name         0
product_name_lenght           0
product_description_lenght    0
product_photos_qty            0
product_weight_g              0
product_length_cm             0
product_height_cm             0
product_width_cm              0
dtype: int64

olist_order_reviews_dataset¶

  • Nulos na tabela: olist_order_reviews_dataset
  • review_id 0
  • order_id 0
  • review_score 0
  • review_comment_title 87656
  • review_comment_message 58247
  • review_creation_date 0
  • review_answer_timestamp 0
  • dtype: int64

Como podemos observar no dataset abaixo, os nulos são referentes as reviews dos clientes, que são bem comuns de não serem preenchidas, dado esse cenário optarei por manter os nulos

In [286]:
dataframes['olist_order_reviews_dataset']
Out[286]:
review_id order_id review_score review_comment_title review_comment_message review_creation_date review_answer_timestamp
0 7bc2406110b926393aa56f80a40eba40 73fc7af87114b39712e6da79b0a377eb 4 NaN NaN 2018-01-18 00:00:00 2018-01-18 21:46:59
1 80e641a11e56f04c1ad469d5645fdfde a548910a1c6147796b98fdf73dbeba33 5 NaN NaN 2018-03-10 00:00:00 2018-03-11 03:05:13
2 228ce5500dc1d8e020d8d1322874b6f0 f9e4b658b201a9f2ecdecbb34bed034b 5 NaN NaN 2018-02-17 00:00:00 2018-02-18 14:36:24
3 e64fb393e7b32834bb789ff8bb30750e 658677c97b385a9be170737859d3511b 5 NaN Recebi bem antes do prazo estipulado. 2017-04-21 00:00:00 2017-04-21 22:02:06
4 f7c4243c7fe1938f181bec41a392bdeb 8e6bfb81e283fa7e4f11123a3fb894f1 5 NaN Parabéns lojas lannister adorei comprar pela I... 2018-03-01 00:00:00 2018-03-02 10:26:53
... ... ... ... ... ... ... ...
99219 574ed12dd733e5fa530cfd4bbf39d7c9 2a8c23fee101d4d5662fa670396eb8da 5 NaN NaN 2018-07-07 00:00:00 2018-07-14 17:18:30
99220 f3897127253a9592a73be9bdfdf4ed7a 22ec9f0669f784db00fa86d035cf8602 5 NaN NaN 2017-12-09 00:00:00 2017-12-11 20:06:42
99221 b3de70c89b1510c4cd3d0649fd302472 55d4004744368f5571d1f590031933e4 5 NaN Excelente mochila, entrega super rápida. Super... 2018-03-22 00:00:00 2018-03-23 09:10:43
99222 1adeb9d84d72fe4e337617733eb85149 7725825d039fc1f0ceb7635e3f7d9206 4 NaN NaN 2018-07-01 00:00:00 2018-07-02 12:59:13
99223 efe49f1d6f951dd88b51e6ccd4cc548f 90531360ecb1eec2a1fbb265a0db0508 1 NaN meu produto chegou e ja tenho que devolver, po... 2017-07-03 00:00:00 2017-07-03 21:01:49

99224 rows × 7 columns

Vamos observar abaixo a quantidade de duplicatas nas tabelas do dicionario de df¶

In [287]:
# Verificando duplicatas
for tabela, df in dataframes.items():
    print(f"Duplicatas na tabela: {tabela}")
    print(df.duplicated().sum())
    print("---------------" * 10)
Duplicatas na tabela: olist_order_payments_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_order_items_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_orders_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: product_category_name_translation
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_products_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_sellers_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_order_reviews_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_geolocation_dataset
261831
------------------------------------------------------------------------------------------------------------------------------------------------------
Duplicatas na tabela: olist_customers_dataset
0
------------------------------------------------------------------------------------------------------------------------------------------------------
In [288]:
# Ordenarei as duplicatas para ficarem juntas
geoloc = dataframes['olist_geolocation_dataset'].sort_values(by=dataframes['olist_geolocation_dataset'].columns.tolist())

# Mostrar apenas duplicatas ordenadas
geoloc[geoloc.duplicated(keep=False)]
Out[288]:
geolocation_zip_code_prefix geolocation_lat geolocation_lng geolocation_city geolocation_state
519 1001 -23.551337 -46.634027 sao paulo SP
583 1001 -23.551337 -46.634027 sao paulo SP
818 1001 -23.551337 -46.634027 sao paulo SP
206 1001 -23.550498 -46.634338 sao paulo SP
429 1001 -23.550498 -46.634338 sao paulo SP
... ... ... ... ... ...
999899 99980 -28.387432 -51.847727 david canabarro RS
999958 99980 -28.387059 -51.848964 david canabarro RS
1000129 99980 -28.387059 -51.848964 david canabarro RS
999775 99980 -28.386689 -51.847091 david canabarro RS
1000133 99980 -28.386689 -51.847091 david canabarro RS

390005 rows × 5 columns

Temos exatos dados duplicatos na tabela.¶

É normal termos o mesmo cep no nosso dataset, afinal mais de uma pessoa pode morar no mesmo endereço em casas diferentes, que resultariam diretamente no mesmo cep, que na nossa tabela esta definida como zip code, porém como os dados de geolocalização são identicos temos que estão indexados na mesma casa, e por isso irei remover tais dados para limpar os dados idênticos.

In [289]:
# Excluindo duplicatas
dataframes['olist_geolocation_dataset'] = dataframes['olist_geolocation_dataset'].drop_duplicates()
In [290]:
# agora temos os dados limpos de duplicatas.
dataframes['olist_geolocation_dataset'].duplicated().sum()
Out[290]:
np.int64(0)

Farei a normalização das colunas¶

Para fazer isso irei utilizar o modulo info no dataset para conseguir visualizar os tipos de colunas em que podem ou devem ser alteradas

In [291]:
for tabela, df in dataframes.items():
    print(f'Nome da tabela: {tabela}')
    print(df.info())
    print('-----' * 10)
Nome da tabela: olist_order_payments_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 103886 entries, 0 to 103885
Data columns (total 5 columns):
 #   Column                Non-Null Count   Dtype  
---  ------                --------------   -----  
 0   order_id              103886 non-null  object 
 1   payment_sequential    103886 non-null  int64  
 2   payment_type          103886 non-null  object 
 3   payment_installments  103886 non-null  int64  
 4   payment_value         103886 non-null  float64
dtypes: float64(1), int64(2), object(2)
memory usage: 4.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_order_items_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 112650 entries, 0 to 112649
Data columns (total 7 columns):
 #   Column               Non-Null Count   Dtype  
---  ------               --------------   -----  
 0   order_id             112650 non-null  object 
 1   order_item_id        112650 non-null  int64  
 2   product_id           112650 non-null  object 
 3   seller_id            112650 non-null  object 
 4   shipping_limit_date  112650 non-null  object 
 5   price                112650 non-null  float64
 6   freight_value        112650 non-null  float64
dtypes: float64(2), int64(1), object(4)
memory usage: 6.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_orders_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 99281 entries, 0 to 99440
Data columns (total 10 columns):
 #   Column                         Non-Null Count  Dtype         
---  ------                         --------------  -----         
 0   order_id                       99281 non-null  object        
 1   customer_id                    99281 non-null  object        
 2   order_status                   99281 non-null  object        
 3   order_purchase_timestamp       99281 non-null  object        
 4   order_approved_at              99281 non-null  datetime64[ns]
 5   order_delivered_carrier_date   97644 non-null  datetime64[ns]
 6   order_delivered_customer_date  96462 non-null  datetime64[ns]
 7   order_estimated_delivery_date  99281 non-null  object        
 8   enviados                       99281 non-null  int64         
 9   entregues                      99281 non-null  int64         
dtypes: datetime64[ns](3), int64(2), object(5)
memory usage: 8.3+ MB
None
--------------------------------------------------
Nome da tabela: product_category_name_translation
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 71 entries, 0 to 70
Data columns (total 2 columns):
 #   Column                         Non-Null Count  Dtype 
---  ------                         --------------  ----- 
 0   product_category_name          71 non-null     object
 1   product_category_name_english  71 non-null     object
dtypes: object(2)
memory usage: 1.2+ KB
None
--------------------------------------------------
Nome da tabela: olist_products_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 32340 entries, 0 to 32950
Data columns (total 9 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   product_id                  32340 non-null  object 
 1   product_category_name       32340 non-null  object 
 2   product_name_lenght         32340 non-null  float64
 3   product_description_lenght  32340 non-null  float64
 4   product_photos_qty          32340 non-null  float64
 5   product_weight_g            32340 non-null  float64
 6   product_length_cm           32340 non-null  float64
 7   product_height_cm           32340 non-null  float64
 8   product_width_cm            32340 non-null  float64
dtypes: float64(7), object(2)
memory usage: 2.5+ MB
None
--------------------------------------------------
Nome da tabela: olist_sellers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3095 entries, 0 to 3094
Data columns (total 4 columns):
 #   Column                  Non-Null Count  Dtype 
---  ------                  --------------  ----- 
 0   seller_id               3095 non-null   object
 1   seller_zip_code_prefix  3095 non-null   int64 
 2   seller_city             3095 non-null   object
 3   seller_state            3095 non-null   object
dtypes: int64(1), object(3)
memory usage: 96.8+ KB
None
--------------------------------------------------
Nome da tabela: olist_order_reviews_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99224 entries, 0 to 99223
Data columns (total 7 columns):
 #   Column                   Non-Null Count  Dtype 
---  ------                   --------------  ----- 
 0   review_id                99224 non-null  object
 1   order_id                 99224 non-null  object
 2   review_score             99224 non-null  int64 
 3   review_comment_title     11568 non-null  object
 4   review_comment_message   40977 non-null  object
 5   review_creation_date     99224 non-null  object
 6   review_answer_timestamp  99224 non-null  object
dtypes: int64(1), object(6)
memory usage: 5.3+ MB
None
--------------------------------------------------
Nome da tabela: olist_geolocation_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 738332 entries, 0 to 1000161
Data columns (total 5 columns):
 #   Column                       Non-Null Count   Dtype  
---  ------                       --------------   -----  
 0   geolocation_zip_code_prefix  738332 non-null  int64  
 1   geolocation_lat              738332 non-null  float64
 2   geolocation_lng              738332 non-null  float64
 3   geolocation_city             738332 non-null  object 
 4   geolocation_state            738332 non-null  object 
dtypes: float64(2), int64(1), object(2)
memory usage: 33.8+ MB
None
--------------------------------------------------
Nome da tabela: olist_customers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 5 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   customer_id               99441 non-null  object
 1   customer_unique_id        99441 non-null  object
 2   customer_zip_code_prefix  99441 non-null  int64 
 3   customer_city             99441 non-null  object
 4   customer_state            99441 non-null  object
dtypes: int64(1), object(4)
memory usage: 3.8+ MB
None
--------------------------------------------------
In [292]:
# Os tipos dos dados nas tabelas estão normalizados
dataframes['olist_order_payments_dataset'].head()
Out[292]:
order_id payment_sequential payment_type payment_installments payment_value
0 b81ef226f3fe1789b1e8b2acac839d17 1 credit_card 8 99.33
1 a9810da82917af2d9aefd1278f1dcfa0 1 credit_card 1 24.39
2 25e8ea4e93396b6fa0d3dd708e76c1bd 1 credit_card 1 65.71
3 ba78997921bbcdc1373bb41e913ab953 1 credit_card 8 107.78
4 42fdf880ba16b47b59251dd489d4441a 1 credit_card 2 128.45
In [293]:
dataframes['olist_order_items_dataset'].head()
Out[293]:
order_id order_item_id product_id seller_id shipping_limit_date price freight_value
0 00010242fe8c5a6d1ba2dd792cb16214 1 4244733e06e7ecb4970a6e2683c13e61 48436dade18ac8b2bce089ec2a041202 2017-09-19 09:45:35 58.90 13.29
1 00018f77f2f0320c557190d7a144bdd3 1 e5f2d52b802189ee658865ca93d83a8f dd7ddc04e1b6c2c614352b383efe2d36 2017-05-03 11:05:13 239.90 19.93
2 000229ec398224ef6ca0657da4fc703e 1 c777355d18b72b67abbeef9df44fd0fd 5b51032eddd242adc84c38acab88f23d 2018-01-18 14:48:30 199.00 17.87
3 00024acbcdf0a6daa1e931b038114c75 1 7634da152a4610f1595efa32f14722fc 9d7a1d34a5052409006425275ba1c2b4 2018-08-15 10:10:18 12.99 12.79
4 00042b26cf59d7ce69dfabb4e55b4fd9 1 ac6c3623068f30de03045865e4e10089 df560393f3a51e74553ab94004ba5c87 2017-02-13 13:57:51 199.90 18.14
In [294]:
# temos a coluna shipping_limit_date como object, transformarei a mesma em datetime
dataframes['olist_order_items_dataset']['shipping_limit_date'] = pd.to_datetime(dataframes['olist_order_items_dataset']['shipping_limit_date'], errors='coerce')
In [295]:
# Faltam 2 colunas serem mudadas para datetime, order_purchase_timestamp e order_estimated_delivery_date
dataframes['olist_orders_dataset'].head()
Out[295]:
order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date enviados entregues
0 e481f51cbdc54678b7cc49136f2d6af7 9ef432eb6251297304e76186b10a928d delivered 2017-10-02 10:56:33 2017-10-02 11:07:15 2017-10-04 19:55:00 2017-10-10 21:25:13 2017-10-18 00:00:00 1 1
1 53cdb2fc8bc7dce0b6741e2150273451 b0830fb4747a6c6d20dea0b8c802d7ef delivered 2018-07-24 20:41:37 2018-07-26 03:24:27 2018-07-26 14:31:00 2018-08-07 15:27:45 2018-08-13 00:00:00 1 1
2 47770eb9100c2d0c44946d9cf07ec65d 41ce2a54c0b03bf3443c3d931a367089 delivered 2018-08-08 08:38:49 2018-08-08 08:55:23 2018-08-08 13:50:00 2018-08-17 18:06:29 2018-09-04 00:00:00 1 1
3 949d5b44dbf5de918fe9c16f97b45f8a f88197465ea7920adcdbec7375364d82 delivered 2017-11-18 19:28:06 2017-11-18 19:45:59 2017-11-22 13:39:59 2017-12-02 00:28:42 2017-12-15 00:00:00 1 1
4 ad21c59c0840e6cb83a9ceb5573f8159 8ab97904e6daea8866dbdbc4fb7aad2c delivered 2018-02-13 21:18:39 2018-02-13 22:20:29 2018-02-14 19:46:34 2018-02-16 18:17:02 2018-02-26 00:00:00 1 1
In [296]:
# Transformando as colunas object em datetime
dataframes['olist_orders_dataset']['order_purchase_timestamp'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_purchase_timestamp'], errors='coerce')
dataframes['olist_orders_dataset']['order_estimated_delivery_date'] = pd.to_datetime(dataframes['olist_orders_dataset']['order_estimated_delivery_date'], errors='coerce')
In [297]:
# Os dados estão normalizados na tabela
dataframes['product_category_name_translation'].head()
Out[297]:
product_category_name product_category_name_english
0 beleza_saude health_beauty
1 informatica_acessorios computers_accessories
2 automotivo auto
3 cama_mesa_banho bed_bath_table
4 moveis_decoracao furniture_decor
In [298]:
# Os dados estão normalizados
dataframes['olist_products_dataset'].head()
Out[298]:
product_id product_category_name product_name_lenght product_description_lenght product_photos_qty product_weight_g product_length_cm product_height_cm product_width_cm
0 1e9e8ef04dbcff4541ed26657ea517e5 perfumaria 40.0 287.0 1.0 225.0 16.0 10.0 14.0
1 3aa071139cb16b67ca9e5dea641aaa2f artes 44.0 276.0 1.0 1000.0 30.0 18.0 20.0
2 96bd76ec8810374ed1b65e291975717f esporte_lazer 46.0 250.0 1.0 154.0 18.0 9.0 15.0
3 cef67bcfe19066a932b7673e239eb23d bebes 27.0 261.0 1.0 371.0 26.0 4.0 26.0
4 9dc1a7de274444849c219cff195d0b71 utilidades_domesticas 37.0 402.0 4.0 625.0 20.0 17.0 13.0
In [299]:
# Os dados estão normalizados
dataframes['olist_sellers_dataset'].head()
Out[299]:
seller_id seller_zip_code_prefix seller_city seller_state
0 3442f8959a84dea7ee197c632cb2df15 13023 campinas SP
1 d1b65fc7debc3361ea86b5f14c68d2e2 13844 mogi guacu SP
2 ce3ad9de960102d0677a81f5d0bb7b2d 20031 rio de janeiro RJ
3 c0f3eea2e14555b6faeea3dd58c1b1c3 4195 sao paulo SP
4 51a04a8a6bdcb23deccc82b0b80742cf 12914 braganca paulista SP
In [300]:
# Temos as 2 colunas de data no formato object que irei transforma em datetime
dataframes['olist_order_reviews_dataset'].head()
Out[300]:
review_id order_id review_score review_comment_title review_comment_message review_creation_date review_answer_timestamp
0 7bc2406110b926393aa56f80a40eba40 73fc7af87114b39712e6da79b0a377eb 4 NaN NaN 2018-01-18 00:00:00 2018-01-18 21:46:59
1 80e641a11e56f04c1ad469d5645fdfde a548910a1c6147796b98fdf73dbeba33 5 NaN NaN 2018-03-10 00:00:00 2018-03-11 03:05:13
2 228ce5500dc1d8e020d8d1322874b6f0 f9e4b658b201a9f2ecdecbb34bed034b 5 NaN NaN 2018-02-17 00:00:00 2018-02-18 14:36:24
3 e64fb393e7b32834bb789ff8bb30750e 658677c97b385a9be170737859d3511b 5 NaN Recebi bem antes do prazo estipulado. 2017-04-21 00:00:00 2017-04-21 22:02:06
4 f7c4243c7fe1938f181bec41a392bdeb 8e6bfb81e283fa7e4f11123a3fb894f1 5 NaN Parabéns lojas lannister adorei comprar pela I... 2018-03-01 00:00:00 2018-03-02 10:26:53
In [301]:
# Tranformando em datetime
dataframes['olist_order_reviews_dataset']['review_creation_date'] = pd.to_datetime(dataframes['olist_order_reviews_dataset']['review_creation_date'], errors='coerce')
dataframes['olist_order_reviews_dataset']['review_answer_timestamp'] = pd.to_datetime(dataframes['olist_order_reviews_dataset']['review_answer_timestamp'], errors='coerce')
In [302]:
# Os dados estão normalizados
dataframes['olist_geolocation_dataset'].head()
Out[302]:
geolocation_zip_code_prefix geolocation_lat geolocation_lng geolocation_city geolocation_state
0 1037 -23.545621 -46.639292 sao paulo SP
1 1046 -23.546081 -46.644820 sao paulo SP
2 1046 -23.546129 -46.642951 sao paulo SP
3 1041 -23.544392 -46.639499 sao paulo SP
4 1035 -23.541578 -46.641607 sao paulo SP
In [303]:
# Os dados estão normalizados
dataframes['olist_customers_dataset'].head()
Out[303]:
customer_id customer_unique_id customer_zip_code_prefix customer_city customer_state
0 06b8999e2fba1a1fbc88172c00ba8bc7 861eff4711a542e4b93843c6dd7febb0 14409 franca SP
1 18955e83d337fd6b2def6b18a428ac77 290c77bc529b7ac935b93aa66c333dc3 9790 sao bernardo do campo SP
2 4e7b3e00288586ebd08712fdd0374a03 060e732b5b29e8181a18229c7b0b2b5e 1151 sao paulo SP
3 b2b6027bc5c5109e529d4dc6358b12c3 259dac757896d24d7702b9acbbff3f3c 8775 mogi das cruzes SP
4 4f2d8ab171c80ec8364f7c12e35b23ad 345ecd01c38d18a9036ed96c73b8d066 13056 campinas SP
In [304]:
# Rodarei novamente o loop, para ficar visivel que todas as mudanças foram feitas
for tabela, df in dataframes.items():
    print(f'Nome da tabela: {tabela}')
    print(df.info())
    print('-----' * 10)
Nome da tabela: olist_order_payments_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 103886 entries, 0 to 103885
Data columns (total 5 columns):
 #   Column                Non-Null Count   Dtype  
---  ------                --------------   -----  
 0   order_id              103886 non-null  object 
 1   payment_sequential    103886 non-null  int64  
 2   payment_type          103886 non-null  object 
 3   payment_installments  103886 non-null  int64  
 4   payment_value         103886 non-null  float64
dtypes: float64(1), int64(2), object(2)
memory usage: 4.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_order_items_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 112650 entries, 0 to 112649
Data columns (total 7 columns):
 #   Column               Non-Null Count   Dtype         
---  ------               --------------   -----         
 0   order_id             112650 non-null  object        
 1   order_item_id        112650 non-null  int64         
 2   product_id           112650 non-null  object        
 3   seller_id            112650 non-null  object        
 4   shipping_limit_date  112650 non-null  datetime64[ns]
 5   price                112650 non-null  float64       
 6   freight_value        112650 non-null  float64       
dtypes: datetime64[ns](1), float64(2), int64(1), object(3)
memory usage: 6.0+ MB
None
--------------------------------------------------
Nome da tabela: olist_orders_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 99281 entries, 0 to 99440
Data columns (total 10 columns):
 #   Column                         Non-Null Count  Dtype         
---  ------                         --------------  -----         
 0   order_id                       99281 non-null  object        
 1   customer_id                    99281 non-null  object        
 2   order_status                   99281 non-null  object        
 3   order_purchase_timestamp       99281 non-null  datetime64[ns]
 4   order_approved_at              99281 non-null  datetime64[ns]
 5   order_delivered_carrier_date   97644 non-null  datetime64[ns]
 6   order_delivered_customer_date  96462 non-null  datetime64[ns]
 7   order_estimated_delivery_date  99281 non-null  datetime64[ns]
 8   enviados                       99281 non-null  int64         
 9   entregues                      99281 non-null  int64         
dtypes: datetime64[ns](5), int64(2), object(3)
memory usage: 8.3+ MB
None
--------------------------------------------------
Nome da tabela: product_category_name_translation
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 71 entries, 0 to 70
Data columns (total 2 columns):
 #   Column                         Non-Null Count  Dtype 
---  ------                         --------------  ----- 
 0   product_category_name          71 non-null     object
 1   product_category_name_english  71 non-null     object
dtypes: object(2)
memory usage: 1.2+ KB
None
--------------------------------------------------
Nome da tabela: olist_products_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 32340 entries, 0 to 32950
Data columns (total 9 columns):
 #   Column                      Non-Null Count  Dtype  
---  ------                      --------------  -----  
 0   product_id                  32340 non-null  object 
 1   product_category_name       32340 non-null  object 
 2   product_name_lenght         32340 non-null  float64
 3   product_description_lenght  32340 non-null  float64
 4   product_photos_qty          32340 non-null  float64
 5   product_weight_g            32340 non-null  float64
 6   product_length_cm           32340 non-null  float64
 7   product_height_cm           32340 non-null  float64
 8   product_width_cm            32340 non-null  float64
dtypes: float64(7), object(2)
memory usage: 2.5+ MB
None
--------------------------------------------------
Nome da tabela: olist_sellers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3095 entries, 0 to 3094
Data columns (total 4 columns):
 #   Column                  Non-Null Count  Dtype 
---  ------                  --------------  ----- 
 0   seller_id               3095 non-null   object
 1   seller_zip_code_prefix  3095 non-null   int64 
 2   seller_city             3095 non-null   object
 3   seller_state            3095 non-null   object
dtypes: int64(1), object(3)
memory usage: 96.8+ KB
None
--------------------------------------------------
Nome da tabela: olist_order_reviews_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99224 entries, 0 to 99223
Data columns (total 7 columns):
 #   Column                   Non-Null Count  Dtype         
---  ------                   --------------  -----         
 0   review_id                99224 non-null  object        
 1   order_id                 99224 non-null  object        
 2   review_score             99224 non-null  int64         
 3   review_comment_title     11568 non-null  object        
 4   review_comment_message   40977 non-null  object        
 5   review_creation_date     99224 non-null  datetime64[ns]
 6   review_answer_timestamp  99224 non-null  datetime64[ns]
dtypes: datetime64[ns](2), int64(1), object(4)
memory usage: 5.3+ MB
None
--------------------------------------------------
Nome da tabela: olist_geolocation_dataset
<class 'pandas.core.frame.DataFrame'>
Index: 738332 entries, 0 to 1000161
Data columns (total 5 columns):
 #   Column                       Non-Null Count   Dtype  
---  ------                       --------------   -----  
 0   geolocation_zip_code_prefix  738332 non-null  int64  
 1   geolocation_lat              738332 non-null  float64
 2   geolocation_lng              738332 non-null  float64
 3   geolocation_city             738332 non-null  object 
 4   geolocation_state            738332 non-null  object 
dtypes: float64(2), int64(1), object(2)
memory usage: 33.8+ MB
None
--------------------------------------------------
Nome da tabela: olist_customers_dataset
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 99441 entries, 0 to 99440
Data columns (total 5 columns):
 #   Column                    Non-Null Count  Dtype 
---  ------                    --------------  ----- 
 0   customer_id               99441 non-null  object
 1   customer_unique_id        99441 non-null  object
 2   customer_zip_code_prefix  99441 non-null  int64 
 3   customer_city             99441 non-null  object
 4   customer_state            99441 non-null  object
dtypes: int64(1), object(4)
memory usage: 3.8+ MB
None
--------------------------------------------------

Resultado do Processo de formalização das colunas:¶

No geral os dados do nosso dataset estavem bem estruturados e de acordo com seu tipo, com exceção das colunas de data que estavam em formato de string, modifiquei todas as colunas de data de object para datetime para futuras consultas nos dados.

Fazer um modelo relacional e conectar as tabelas adequadamente¶

Utilizei o site: https://dbdiagram.io/d/DER-OLIST-6826388f5b2fc4582fcf952a para fazer o der desse banco de dados. O arquivo esta anexado na pasta para visualização tanto em formato PDF, Quanto imagem. Utilizei de base para fazer as querys futuras de forma visual, olhando como os dados se relacionavam para fazer os joins

Como o foco principal é fazer análises e não construir um banco de dados mais robusto, irei manter o banco de dados sem relacionamento direto, pois conseguirei fazer joins e manipular as tabelas no sql diretamente.

In [305]:
import sqlite3

con = sqlite3.connect("olist_dataset.db")

for nome_tabela, df in dataframes.items():
    df.to_sql(nome_tabela, con, if_exists='replace', index=False)

Acima importei o sqlite3 e fiz a conexão com um arquivo local .db, que é criado quando executado e se existe será substituido quando o comando ser executado¶

Em seguida utilizo das propriedades do dicionario de chave e valor, para transformar o valor(df) em tabela e linkar o nome da tabela(chave), ao nome da tabela do banco de dados.

Tipos de Análise que poderão ser feitas:¶

  1. Utilizarei o ipython-sql para consultas diretas no banco de dados com "SQL Puro" .
  2. Utilizarei o pandas para fazer consultas no banco de dados em conjunto com SQL.

Exemplos abaixo:

1. ipython-sql¶

In [306]:
#Carrego a extensao sql primeiro e em seguia faço a conexão com o banco de dados
%load_ext sql 
%sql sqlite:///olist_dataset.db
The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Abaixo irei fazer uma consulta com SQL

In [307]:
%%sql
SELECT * FROM olist_customers_dataset LIMIT 5
 * sqlite:///olist_dataset.db
Done.
Out[307]:
customer_id customer_unique_id customer_zip_code_prefix customer_city customer_state
06b8999e2fba1a1fbc88172c00ba8bc7 861eff4711a542e4b93843c6dd7febb0 14409 franca SP
18955e83d337fd6b2def6b18a428ac77 290c77bc529b7ac935b93aa66c333dc3 9790 sao bernardo do campo SP
4e7b3e00288586ebd08712fdd0374a03 060e732b5b29e8181a18229c7b0b2b5e 1151 sao paulo SP
b2b6027bc5c5109e529d4dc6358b12c3 259dac757896d24d7702b9acbbff3f3c 8775 mogi das cruzes SP
4f2d8ab171c80ec8364f7c12e35b23ad 345ecd01c38d18a9036ed96c73b8d066 13056 campinas SP

2. Pandas com SQL¶

In [308]:
pd.read_sql("Select * From olist_customers_dataset;", con).head()
Out[308]:
customer_id customer_unique_id customer_zip_code_prefix customer_city customer_state
0 06b8999e2fba1a1fbc88172c00ba8bc7 861eff4711a542e4b93843c6dd7febb0 14409 franca SP
1 18955e83d337fd6b2def6b18a428ac77 290c77bc529b7ac935b93aa66c333dc3 9790 sao bernardo do campo SP
2 4e7b3e00288586ebd08712fdd0374a03 060e732b5b29e8181a18229c7b0b2b5e 1151 sao paulo SP
3 b2b6027bc5c5109e529d4dc6358b12c3 259dac757896d24d7702b9acbbff3f3c 8775 mogi das cruzes SP
4 4f2d8ab171c80ec8364f7c12e35b23ad 345ecd01c38d18a9036ed96c73b8d066 13056 campinas SP

Descrever os Passos de preparação dos dados adotados¶

Importação de arquivos:¶

Fiz a importação dos 9 arquivos necessários em 3 passos.
1 - Defini a variável path que continha o diretório dos arquivos CSVs.
2 - Utilizei list comprehension para armazenar o nome de todos os arquivos CSV do caminho indexado na lista arquivos.
3 - Criei um dicionário de DataFrames, onde a chave seria o nome do df, e o valor seria o df por si só, com linhas e colunas.

Limpeza dos dados:¶

Nulos:¶

Primeiramente, fiz a verificação de nulos no dataset e notei que havia dados nulos em 3 tabelas:

  • olist_orders_dataset
  • olist_products_dataset
  • olist_order_reviews_dataset

olist_orders_dataset
Optei por excluir os dados nulos da tabela, que só continha 160 registros, pois não eram significativos a ponto de mudar nossa análise, dado que temos um conjunto com 100 mil dados.
Enquanto nas tabelas seguintes, optei por deixar os nulos e criar uma tabela nova para cada, onde criei valores booleanos de 0 e 1 para identificar quando os produtos foram ou não enviados/entregues.

olist_products_dataset
Identifiquei pouquíssimos dados nulos nessa tabela, que não influenciavam na análise posterior. Por isso, com base em meu conhecimento, optei por excluir todos os dados nulos.

olist_order_reviews_dataset
Os dados nulos nessa tabela eram referentes aos comentários de avaliações de produtos, que são comumente deixados em branco. Porém, continham os dados da avaliação geral do produto, que são extremamente relevantes. Como os dados nulos representavam grande parte do conjunto, escolhi mantê-los.

Duplicatas:¶

Os dados duplicados estavam na tabela de geolocalização. Como essa tabela, por si só, não tem um relacionamento direto com as outras, optei por excluir as colunas duplicadas, pois não fazia sentido mantê-las. Esses dados não influenciam nas nossas análises, e, caso essa tabela seja usada no futuro, apresentaria inconsistências se os duplicados fossem mantidos.

Normalização dos dados nas tabelas:¶

Os dados, em geral, estavam bem organizados após serem importados pelo pandas. Porém, as colunas de data estavam como object (strings). Tendo isso em vista, modifiquei todas as colunas de datas de object para datetime durante a normalização.

Fazer um modelo relacional e conectar as tabelas adequadamente:¶

Utilizei o site db.diagram.io para fazer o diagrama de entidade e relacionamento. Porém, por praticidade de análise, e dado que os dados estavam consistentes, optei por não criar o banco de dados manualmente tabela por tabela. Em vez disso, importei as tabelas diretamente do dicionário que criei anteriormente, tornando mais fácil e rápido realizar as análises, já que os joins entre as tabelas poderiam ser feitos da mesma forma.

Não menos importante, defini os tipos de análises que poderiam ser feitas a partir do banco de dados criado, sendo elas:
1- ipython-sql: onde posso escrever código SQL diretamente na célula do Jupyter Notebook.
2- Pandas com SQL: onde posso utilizar o pandas para fazer uma query no banco de dados a partir da conexão criada anteriormente.

Análise Exploratória de Dados¶

Optei por fazer as querys utilizando SQL no Banco de dados criado, e as querys com pandas no dicionário de dataframes, pois ficaria redundante escrever o mesmo código sql no pandas para obter o mesmo resultado¶

a) Qual o volume de pedidos por mês? Existe sazonalidade nas vendas?¶

SQL¶

In [309]:
%%sql
SELECT * FROM olist_orders_dataset limit 1
 * sqlite:///olist_dataset.db
Done.
Out[309]:
order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date enviados entregues
e481f51cbdc54678b7cc49136f2d6af7 9ef432eb6251297304e76186b10a928d delivered 2017-10-02 10:56:33 2017-10-02 11:07:15 2017-10-04 19:55:00 2017-10-10 21:25:13 2017-10-18 00:00:00 1 1
In [310]:
%%sql
SELECT
    strftime('%m', order_approved_at) as mes,
    count(*) as "total de vendas"
FROM olist_orders_dataset
GROUP BY mes
 * sqlite:///olist_dataset.db
Done.
Out[310]:
mes total de vendas
01 7947
02 8471
03 9977
04 9152
05 10759
06 9416
07 10150
08 10968
09 4303
10 4910
11 7395
12 5833
In [311]:
%%sql
SELECT
    (count(*))/ 12 as "media de vendas mensais"
FROM olist_orders_dataset
 * sqlite:///olist_dataset.db
Done.
Out[311]:
media de vendas mensais
8273
In [312]:
%%sql

WITH vendas_por_mes as(
    SELECT
        strftime('%m', order_approved_at) as mes,
        count(*) as total_vendas
    FROM
        olist_orders_dataset
    GROUP BY
        mes
),
media_geral as (
    SELECT
        avg(total_vendas) as media_mensal
    FROM
        vendas_por_mes
)
SELECT 
    v.mes,
    v.total_vendas,
    Round(v.total_vendas / m.media_mensal, 2) as ind_sazonal 
FROM
    vendas_por_mes as v,
    media_geral as m
ORDER BY
    v.mes
 * sqlite:///olist_dataset.db
Done.
Out[312]:
mes total_vendas ind_sazonal
01 7947 0.96
02 8471 1.02
03 9977 1.21
04 9152 1.11
05 10759 1.3
06 9416 1.14
07 10150 1.23
08 10968 1.33
09 4303 0.52
10 4910 0.59
11 7395 0.89
12 5833 0.71

Pandas¶

In [313]:
dataframes['olist_orders_dataset'].head(1)
Out[313]:
order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date enviados entregues
0 e481f51cbdc54678b7cc49136f2d6af7 9ef432eb6251297304e76186b10a928d delivered 2017-10-02 10:56:33 2017-10-02 11:07:15 2017-10-04 19:55:00 2017-10-10 21:25:13 2017-10-18 1 1
In [314]:
dfa = dataframes['olist_orders_dataset']
dfa['mes'] = dfa['order_approved_at'].dt.month
vendas_p_mes = dfa.groupby('mes').size().reset_index(name='total_vendas')
media_mensal = (dfa['order_approved_at'].count()) / 12
vendas_p_mes['indice_sazonal'] = round(vendas_p_mes['total_vendas'] / media_mensal, 2)
vendas_p_mes = vendas_p_mes.sort_values('mes').reset_index(drop=True)

vendas_p_mes
Out[314]:
mes total_vendas indice_sazonal
0 1 7947 0.96
1 2 8471 1.02
2 3 9977 1.21
3 4 9152 1.11
4 5 10759 1.30
5 6 9416 1.14
6 7 10150 1.23
7 8 10968 1.33
8 9 4303 0.52
9 10 4910 0.59
10 11 7395 0.89
11 12 5833 0.71

O código em pandas ficou mais enxuto, porque eu ja tinha feito a query em SQL, então basicamente só fui reescrevendo a query no formato pandas, mas vou explicar o que cada linha faz abaixo.¶

  1. fiz o dataframe dfa, que é literalmente dataframe da questão A, irei repetir em todos os exercícios o mesmo padrão de nome. Que recebe o dataframe (olist_orders_dataset) do dicionário criado no início do projeto.
  2. Criei a coluna mes no dfa, a partir da funcao .dt.month, que extrai o mes de colunas datetime
  3. Crie outro dataframe, que representa as vendas agrupadas por mes, o size conta as vendas por mes e defini o valor do nome da coluna para total_vendas
  4. Calculei a media mensal a partir do primeiro dataset dfa
  5. Adicionei a coluna indice sazonal, que é total de vendas por mes, dividido, pela media mensal. O comando round, server para termos apenas 2 casas decimas
  6. Ordenei o dataframes vendas_p_mes pelo mes, para ficar sequencial.
In [315]:
# Grafico de vendas mensais. 
plt.figure(figsize=(10, 5))
plt.bar(vendas_p_mes['mes'], vendas_p_mes['total_vendas'], color='skyblue')
plt.axhline(y=media_mensal, color='r', linestyle='--', label=f'Média Mensal: {media_mensal:.0f}')
plt.title('Vendas Mensais')
plt.xlabel('Mês')
plt.ylabel('Total de Vendas')
plt.xticks(range(1, 13))
plt.legend()
plt.grid(axis='y', alpha=0.3)
plt.show()

# Grafico de indice sazonal
plt.figure(figsize=(10, 5))
plt.plot(vendas_p_mes['mes'], vendas_p_mes['indice_sazonal'], marker='o', color='green', label='Índice Sazonal')
plt.axhline(y=1, color='gray', linestyle='--', label='Média (1.0)')
plt.title('Sazonalidade das Vendas (Índice)')
plt.xlabel('Mês')
plt.ylabel('Índice Sazonal')
plt.xticks(range(1, 13))
plt.legend()
plt.grid(alpha=0.3)
plt.show()
No description has been provided for this image
No description has been provided for this image

Insights:¶

Temos uma média de 8273 produtos sendo vendidos mensalmente.

Como é possivel observar tanto pelo gráfico de vendas mensais, quanto pelo gráfico do indíce sazonal é que temos uma alta demanda entre o mês 3(março) até o mês 8(agosto). Já nos 2 primeiros meses do ano, temos uma médida de venda dentro do esperado. Porém, nos meses de Setembro até Dezembro temos um indice sazonal menor por decorrência de menos vendas nesses mêses, o que é estranho dado que em datas comemorativas as pessoas tendêm a comprar mais, podemos não estar fazendo um bom marketing nesse momento do ano, e outros concorrentes se sobresaem nesse período.

Mas o que podemos dizer sobre o indice sazonal, é que durante o mês 3 ate o mês 8, devemos preparar nossos estoques pois teremos uma demanda maior de vendas durante esse período, já de Setemebro ate Dezembro, não precisamos de tanto estoque, tendo em vista que vendemos bem menos nesse período.

b) Qual a distribuição do tempo de entrega dos pedidos?¶

SQL¶

In [316]:
%%sql

SELECT
    julianday(order_delivered_customer_date) - julianday(order_approved_at) AS tempo_entrega_dias
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date IS NOT NULL
    AND order_approved_at IS NOT NULL
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
limit 10
 * sqlite:///olist_dataset.db
Done.
Out[316]:
tempo_entrega_dias
8.429143518675119
12.502291666343808
9.382708332967013
13.196331018581986
2.8309375001117587
16.533125000074506
9.981712962966412
8.470405092928559
18.214560185093433
12.644594907294959

Limitei a query abaixo, pois temos muitos valores de dias_ate_entrega, vou fazer isso por legibildiade do código dado que no geral estamos mais interessados nos valores mais ocorrentes.

In [317]:
%%sql 

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    dias_ate_entrega 
LIMIT 30
 * sqlite:///olist_dataset.db
Done.
Out[317]:
dias_ate_entrega total_pedidos
0 158
1 2381
2 3550
3 4321
4 4975
5 6176
6 7580
7 7715
8 6389
9 5869
10 5406
11 4819
12 4471
13 4333
14 3620
15 2887
16 2464
17 2247
18 1940
19 1671
20 1685
21 1408
22 1110
23 987
24 858
25 764
26 675
27 634
28 535
29 443

Vou limitar as primeiras 30 ocorrências ordenando por os dias com mais pedidos¶

In [318]:
%%sql

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    total_pedidos desc
LIMIT 30
 * sqlite:///olist_dataset.db
Done.
Out[318]:
dias_ate_entrega total_pedidos
7 7715
6 7580
8 6389
5 6176
9 5869
10 5406
4 4975
11 4819
12 4471
13 4333
3 4321
14 3620
2 3550
15 2887
16 2464
1 2381
17 2247
18 1940
20 1685
19 1671
21 1408
22 1110
23 987
24 858
25 764
26 675
27 634
28 535
29 443
30 422

Vou fazer o mesmo porém com os ultimos pedidos

In [319]:
%%sql

SELECT
    CAST(julianday(order_delivered_customer_date) - julianday(order_approved_at) as INTEGER) as dias_ate_entrega,
    COUNT(*) as total_pedidos
FROM
    olist_orders_dataset
WHERE
    order_delivered_customer_date is not null
    AND order_approved_at is not null
    AND (julianday(order_delivered_customer_date) - julianday(order_approved_at)) >= 0
GROUP BY
    dias_ate_entrega
ORDER BY
    total_pedidos asc
LIMIT 30
 * sqlite:///olist_dataset.db
Done.
Out[319]:
dias_ate_entrega total_pedidos
87 1
90 1
91 1
92 1
100 1
101 1
105 1
108 1
109 1
110 1
115 1
117 1
118 1
123 1
125 1
126 1
129 1
132 1
133 1
135 1
136 1
140 1
142 1
148 1
165 1
166 1
168 1
171 1
172 1
173 1

Pandas¶

In [320]:
dfb = dataframes['olist_orders_dataset']
dfb_nn = dfb[dfb['order_delivered_customer_date'].notna() & dfb['order_approved_at'].notna()]
dfb_nn['dias_ate_entrega'] = (dfb_nn['order_delivered_customer_date'] - dfb_nn['order_approved_at']).dt.days
dfb_nn = dfb_nn[dfb_nn['dias_ate_entrega'] >= 0]

queryb = (
    dfb_nn
    .groupby('dias_ate_entrega')
    .size()
    .reset_index(name='total_pedidos')
    .sort_values('total_pedidos', ascending=False)
)

queryb.head(30)
/tmp/ipykernel_5750/1770269448.py:3: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

Out[320]:
dias_ate_entrega total_pedidos
7 7 7715
6 6 7580
8 8 6389
5 5 6176
9 9 5869
10 10 5406
4 4 4975
11 11 4819
12 12 4471
13 13 4333
3 3 4321
14 14 3620
2 2 3550
15 15 2887
16 16 2464
1 1 2381
17 17 2247
18 18 1940
20 20 1685
19 19 1671
21 21 1408
22 22 1110
23 23 987
24 24 858
25 25 764
26 26 675
27 27 634
28 28 535
29 29 443
30 30 422

Minha querry no pandas assim como no exercício passado foi feita baseada na minha query sql.

  1. defini o dfb
  2. Reatribui o dfb, para outro dataset sem os valores nulos nas colunas de interesse.
  3. Criei a coluna 'dias_ate_entrega', que utlizado o método .dt.days para extrair o dia da subtração das datas.
  4. Filtrei os dias de entrega maiores ou iguais a zero.
  5. Fiz a query panda pelo agrupamento dos dias de entrega, ordenado pelos total dos pedidos assim como na query sql.

Utilizei somente 30 linhas na head, para ficar igual a ultima query do sql, mas claramente, caso só visualizamos o dataframe sem limitações iria ser igual ao da primeira query SQL, porem para não extender o notebook com o mesmo resultado optei por só deixar as 30 primeiras linhas.

In [321]:
# Histograma
sns.histplot(dfb_nn['dias_ate_entrega'], bins=60, kde=True, color='steelblue')
plt.title('Distribuição de Prazos de Entrega', fontsize=16)
plt.xlabel('Dias desde Aprovação até Entrega', fontsize=12)
plt.ylabel('Número de Pedidos', fontsize=12)
plt.grid(linestyle='--', alpha=0.7)
plt.figure(figsize=(24, 12))

plt.tight_layout()
plt.show()
No description has been provided for this image
<Figure size 2400x1200 with 0 Axes>

Insight b¶

Temos uma alta concentração de entregas entre 0-50 dias, com picos na faixa de 7-30.

Temos uma cauda longa a direita nesse histograma, o que nos diz que existem alguns pedidos outliers que distoam muito da média, porem que ocorrem, como conseguimos ver na query sql anterior, existe um total de pedidos 1-2 que foram engregues com uma data muito longa, e por isso não aparecem como barras no gráfico, pois são muito pequenos, porém estão ali.

c) Qual a relação entre o valor do frete e a distância de entrega?¶

Para ser honesto, não sei se todos os exercícios precisam ser resolvidos em python e sql, ou se é para utilizar a melhor ferramenta para cada situação, porém nesse caso utilizarei somente o pandas, pois existem muitos dados e eu quero que fique visivel a correlaçao dada.

In [322]:
%%sql

WITH geo_coordenadas AS (
    SELECT
        geolocation_zip_code_prefix,
        AVG(geolocation_lat) AS lat,
        AVG(geolocation_lng) AS lng
    FROM olist_geolocation_dataset
    GROUP BY geolocation_zip_code_prefix
),
cliente_coordenadas AS (
    SELECT
        cli.customer_id,
        geo.lat AS customer_lat,
        geo.lng AS customer_lng
    FROM olist_customers_dataset cli
    JOIN geo_coordenadas geo
    ON cli.customer_zip_code_prefix = geo.geolocation_zip_code_prefix
),
vendedor_coordenadas AS (
    SELECT
        vend.seller_id,
        geo.lat AS seller_lat,
        geo.lng AS seller_lng
    FROM olist_sellers_dataset vend
    JOIN geo_coordenadas geo ON vend.seller_zip_code_prefix = geo.geolocation_zip_code_prefix
),
frete_com_distancia AS (
    SELECT
        oi.order_id,
        oi.freight_value,
        6371 * 2 * ASIN(SQRT(
            POWER(SIN(RADIANS(cc.customer_lat - sc.seller_lat) / 2), 2) +
            COS(RADIANS(sc.seller_lat)) * COS(RADIANS(cc.customer_lat)) *
            POWER(SIN(RADIANS(cc.customer_lng - sc.seller_lng) / 2), 2)
        )) AS distancia_km
    FROM olist_order_items_dataset oi
    JOIN olist_orders_dataset o ON oi.order_id = o.order_id
    JOIN cliente_coordenadas cc ON o.customer_id = cc.customer_id
    JOIN vendedor_coordenadas sc ON oi.seller_id = sc.seller_id
    WHERE oi.freight_value IS NOT NULL
)

SELECT 
    FLOOR(distancia_km / 100) * 100 AS faixa_distancia_km,
    AVG(freight_value) AS media_frete,
    COUNT(*) AS total_pedidos
FROM frete_com_distancia
WHERE distancia_km IS NOT NULL
GROUP BY faixa_distancia_km
ORDER BY faixa_distancia_km
 * sqlite:///olist_dataset.db
Done.
Out[322]:
faixa_distancia_km media_frete total_pedidos
0 11.757457602896341 20992
100 15.086909044520985 7839
200 17.738878697842658 7741
300 19.233853331663326 15968
400 19.908209614404623 11774
500 19.726619595915707 9206
600 21.58151177422254 5563
700 22.12764135099832 5359
800 21.18396746299282 6823
900 22.81165919282511 3122
1000 24.65998511904762 2016
1100 26.913206249999998 1600
1200 27.54624617737003 1308
1300 26.89457425742574 1010
1400 25.958059171597636 1690
1500 29.941724137931036 754
1600 33.175266362252664 657
1700 37.94852631578947 665
1800 38.36524011299435 708
1900 37.70882558139535 860
2000 38.867048681541576 986
2100 34.23311141304348 1472
2200 37.09705521472392 978
2300 31.190757910228108 1359
2400 32.63035168195719 654
2500 42.45066666666666 225
2600 37.395551839464886 299
2700 45.484078947368424 152
2800 49.69102272727273 88
2900 56.03830188679245 53
3000 60.645714285714284 28
3100 42.949866666666665 75
3200 41.318125 16
3300 33.96806451612903 31
3400 42.98 1
3500 56.69 3
3900 34.88 1
4700 20.8 1
4800 58.08 1
4900 29.244999999999997 2
8600 25.6 1

Explicar a query¶

Fiz um conjunto de 4 ctes para extrair o resultado.

  1. Altitude e Longtitude por cep
  2. Atribui aos clientes as coordeenadas calculadas anteriormente fazendo um join pelo cep
  3. A mesma CTE anterior, porem dessa vez para vendedores
  4. Essa aqui foi a mais complexa, pois tive que usar a fórmula de haversine que eu nunca tinha visto antes para calcular a distancia em KM. Sendo bem honesto, utilizei do chatgpt para fazer essa query e comparei com a formula e estava bem decente, então segui, de resto foi só fazer os joins nas colunas. Nessa coluna temos o id do pedido, o valor do frete e a distância correspondendente
  5. Aqui eu agrupei a distancia em faixas de 100km para o resultado não ficar estratosféricamente grande, media do frete e contagem de pedidos, ordenas pela distancia.

Pandas¶

In [323]:
# Vou reoodernar os dataset em nomes pequenos para ficar mais fácil de acessar nas querys seguintes.
pag = dataframes['olist_order_payments_dataset']
items = dataframes['olist_order_items_dataset']
ped = dataframes['olist_orders_dataset']
ctg_nome = dataframes['product_category_name_translation']
prod = dataframes['olist_products_dataset']
vend = dataframes['olist_sellers_dataset']
reviews = dataframes['olist_order_reviews_dataset']
geo = dataframes['olist_geolocation_dataset']
cli = dataframes['olist_customers_dataset']
In [324]:
import pandas as pd
import numpy as np

# Calcula as coordenadas médias por CEP
coordenadas_cep = geo.groupby('geolocation_zip_code_prefix').agg({
    'geolocation_lat': 'mean',
    'geolocation_lng': 'mean'
}).reset_index()
coordenadas_cep.columns = ['cep', 'lat', 'lng']

# Adiciona coordenadas aos clientes
clientes_coord = pd.merge(
    cli,
    coordenadas_cep,
    left_on='customer_zip_code_prefix',
    right_on='cep',
    how='left'
)[['customer_id', 'lat', 'lng']].rename(columns={
    'lat': 'cliente_lat',
    'lng': 'cliente_lng'
})

# Adiciona coordenadas aos vendedores
vendedores_coord = pd.merge(
    vend,
    coordenadas_cep,
    left_on='seller_zip_code_prefix',
    right_on='cep',
    how='left'
)[['seller_id', 'lat', 'lng']].rename(columns={
    'lat': 'vendedor_lat',
    'lng': 'vendedor_lng'
})

# Junta todos os dados
dados = (items.merge(ped, on='order_id')
              .merge(clientes_coord, on='customer_id')
              .merge(vendedores_coord, on='seller_id')
              .dropna(subset=['freight_value']))

# Calcula distância usando fórmula haversine
def calcular_distancia(row):
    lat1, lon1 = row['vendedor_lat'], row['vendedor_lng']
    lat2, lon2 = row['cliente_lat'], row['cliente_lng']
    
    lat1, lon1, lat2, lon2 = map(np.radians, [lat1, lon1, lat2, lon2])
    dlat, dlon = lat2 - lat1, lon2 - lon1
    
    a = np.sin(dlat/2)**2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon/2)**2
    return 6371 * 2 * np.arcsin(np.sqrt(a))

dados['distancia_km'] = dados.apply(calcular_distancia, axis=1).dropna()

# Agrupa por faixas de 100km e calcula estatísticas
resultado = (dados.assign(faixa_distancia=lambda x: (x['distancia_km'] // 100) * 100)
                 .groupby('faixa_distancia')
                 .agg(frete_medio=('freight_value', 'mean'),
                      total_pedidos=('order_id', 'count'))
                 .sort_index())

resultado
Out[324]:
frete_medio total_pedidos
faixa_distancia
0.0 11.757458 20992
100.0 15.086909 7839
200.0 17.738879 7741
300.0 19.233853 15968
400.0 19.908210 11774
500.0 19.726620 9206
600.0 21.581512 5563
700.0 22.127641 5359
800.0 21.183967 6823
900.0 22.811659 3122
1000.0 24.659985 2016
1100.0 26.913206 1600
1200.0 27.546246 1308
1300.0 26.894574 1010
1400.0 25.958059 1690
1500.0 29.941724 754
1600.0 33.175266 657
1700.0 37.948526 665
1800.0 38.365240 708
1900.0 37.708826 860
2000.0 38.867049 986
2100.0 34.233111 1472
2200.0 37.097055 978
2300.0 31.190758 1359
2400.0 32.630352 654
2500.0 42.450667 225
2600.0 37.395552 299
2700.0 45.484079 152
2800.0 49.691023 88
2900.0 56.038302 53
3000.0 60.645714 28
3100.0 42.949867 75
3200.0 41.318125 16
3300.0 33.968065 31
3400.0 42.980000 1
3500.0 56.690000 3
3900.0 34.880000 1
4700.0 20.800000 1
4800.0 58.080000 1
4900.0 29.245000 2
8600.0 25.600000 1
In [325]:
plt.figure(figsize=(10, 5))
sns.regplot(x=resultado.index, y=resultado['frete_medio'], 
            scatter_kws={'s': 100}, line_kws={'color': 'red'})
plt.xlabel('Distância (faixas de 100 km)')
plt.ylabel('Frete Médio (R$)')
plt.title('Relação entre Distância e Frete Médio')
plt.grid(True)
plt.show()
No description has been provided for this image

Insight c)¶

Temos uma relação de crescimento do valor do frete conforme a distância de entrega, o que faz sentido porque quanto mais longe mais caro fica.

Além disso, também podemos observar que conforme o a distância cresce, o número de pedidos diminui, ou seja, nós temos que as pessoas usualmente compram de vendedores próximos.

d) Quais são as categorias de produtos mais vendidas em termos de faturamento?¶

In [326]:
%%sql

SELECT
    products.product_category_name as categoria,
    round(sum(items.price), 2) as faturamento
FROM
    olist_order_items_dataset as items
    left join
    olist_products_dataset as products
    on items.product_id = products.product_id
GROUP BY 
    categoria
ORDER BY
    faturamento DESC
limit 10
 * sqlite:///olist_dataset.db
Done.
Out[326]:
categoria faturamento
beleza_saude 1258681.34
relogios_presentes 1205005.68
cama_mesa_banho 1036988.68
esporte_lazer 988048.97
informatica_acessorios 911954.32
moveis_decoracao 729762.49
cool_stuff 635290.85
utilidades_domesticas 632248.66
automotivo 592720.11
ferramentas_jardim 485256.46

pandas¶

In [327]:
merged = items.merge(prod, on='product_id', how='left')

# Agrupar por categoria e somar o faturamento
faturamento_por_categoria = (
    merged.groupby('product_category_name')['price']
    .sum()
    .round(2)
    .sort_values(ascending=False)
    .head(10)
    .reset_index()
    .rename(columns={'product_category_name': 'categoria', 'price': 'faturamento'})
)
In [328]:
# Grafico de faturamento com base nas categorias
sns.set(style="darkgrid")
plt.figure(figsize=(12, 6))
sns.barplot(
    data=faturamento_por_categoria,
    x='faturamento',
    y='categoria',
    palette='crest',
    hue='categoria'
)

plt.title('Top 10 Categorias com Maior Faturamento', fontsize=14)
plt.xlabel('Faturamento (R$)', fontsize=12)
plt.ylabel('Categoria', fontsize=12)
plt.tight_layout()
plt.show()
No description has been provided for this image

Insight D¶

Temos as 10 Categorias com maior faturamento, as categorias de beleza_saude e relogios_presentes tem o maior valor faturamento, o que mostra que temos uma grande demanda por esse tipo de produto o que deve gerar potencial camapanhas de marketing visto que temos muita demanda e muito publico para comprar esses produtos.

Em comparação temos que esporte e informática estão acima da média de faturamento, o que posiciona essas categorias como categorias em potencial crescimento exponencial, talvez se conseguirmos gerar campanhas de marketing, promoções e expansão de produtos possamos vender mais e mais.

A Pior categoria dentre as top 10 é a de ferramentas_jardim o que faz sentido tendo em vista que não é algo que compramos recorrentemente, e sim quando precisamos, geralmente essas ferramentas são duráveis, então não haverá tanta recorrência de compra quanto as outras.

e) Quais estados brasileiros possuem o maior valor médio de pedido?¶

SQL¶

In [329]:
%%sql

SELECT
    c.customer_state AS estado,
    ROUND(SUM(oi.price) / COUNT(DISTINCT o.order_id), 2) AS valor_medio_pedido
FROM
    olist_orders_dataset AS o
JOIN
    olist_customers_dataset AS c
    ON o.customer_id = c.customer_id
JOIN
    olist_order_items_dataset AS oi
    ON o.order_id = oi.order_id
GROUP BY
    estado
ORDER BY
    valor_medio_pedido DESC
 * sqlite:///olist_dataset.db
Done.
Out[329]:
estado valor_medio_pedido
PB 216.67
AP 198.15
AC 197.32
AL 195.41
RO 186.8
PA 184.48
TO 177.86
PI 176.3
MT 173.26
RN 172.27
CE 171.32
SE 170.79
RR 170.21
MS 164.76
MA 161.8
PE 159.46
BA 152.28
AM 152.09
GO 146.78
SC 144.12
RJ 142.94
DF 142.4
RS 138.08
MG 137.34
PR 136.67
ES 135.82
SP 125.75

Quero fazer um adendo aqui porque fiz uma query anterior e achei interessante¶

In [330]:
%%sql
SELECT
    cli.customer_state AS estado,
    COUNT(*) AS numero_de_pedidos
FROM
    olist_customers_dataset AS cli
LEFT JOIN
    olist_orders_dataset AS ped
    ON cli.customer_id = ped.customer_id
GROUP BY
    estado
ORDER BY
    numero_de_pedidos DESC

limit 5
 * sqlite:///olist_dataset.db
Done.
Out[330]:
estado numero_de_pedidos
SP 41746
RJ 12852
MG 11635
RS 5466
PR 5045

Tinha feito essa query antes para ver o número de pedidos, e achei interesasnte que mesmo são paulo sendo o estado que mais vende ele é o último colocado em valor medio por produto, faz sentido, porque existem muito mais compras dado que compras pequenas puxam a média para baixo, mas não esperava que seria o último estado. Achei o insight interessante.

pandas¶

In [331]:
# Merge dos dataframes
merged = ped.merge(cli, on='customer_id') \
               .merge(items, on='order_id')

# Agrupar por estado e order_id 
pedido_por_estado = (
    merged.groupby(['customer_state', 'order_id'])['price'].sum().reset_index()
)

# agrupar por estado para calcular a média dos valores dos pedidos
resultado = (
    pedido_por_estado.groupby('customer_state')['price']
    .mean()
    .round(2)
    .reset_index()
    .rename(columns={'customer_state': 'estado', 'price': 'valor_medio_pedido'})
    .sort_values(by='valor_medio_pedido', ascending=False)
)

resultado
Out[331]:
estado valor_medio_pedido
14 PB 216.67
3 AP 198.15
0 AC 197.32
1 AL 195.41
20 RO 186.80
13 PA 184.48
26 TO 177.86
16 PI 176.30
12 MT 173.26
19 RN 172.27
5 CE 171.32
24 SE 170.79
21 RR 170.20
11 MS 164.76
9 MA 161.80
15 PE 159.46
4 BA 152.28
2 AM 152.09
8 GO 146.78
23 SC 144.12
18 RJ 142.94
6 DF 142.40
22 RS 138.08
10 MG 137.34
17 PR 136.67
7 ES 135.82
25 SP 125.75
In [332]:
# Merge
merged = ped.merge(cli, on='customer_id') \
               .merge(items, on='order_id')

# Agrupar por estado e order_id para somar os preços por pedido
pedido_por_estado = (
    merged.groupby(['customer_state', 'order_id'])['price'].sum().reset_index()
)

# Agora agrupar por estado para calcular a média dos valores dos pedidos
resultado = (
    pedido_por_estado.groupby('customer_state')['price']
    .mean()
    .round(2)
    .reset_index()
    .rename(columns={'customer_state': 'estado', 'price': 'valor_medio_pedido'})
    .sort_values(by='valor_medio_pedido', ascending=False)
)
In [333]:
# gráfico de barras
plt.figure(figsize=(12,6))
plt.bar(resultado['estado'], resultado['valor_medio_pedido'], color='skyblue')
plt.xlabel('Estado')
plt.ylabel('Valor Médio do Pedido')
plt.title('Valor Médio do Pedido por Estado')
plt.xticks(rotation=45)
plt.grid(axis='y', linestyle='--', alpha=0.7)
plt.tight_layout()
plt.show()
No description has been provided for this image

Insights e)¶

Temos um valor médio de produtos maior na região Nordeste e Norte do país, porém são menos pessoas comprando produtos, temos um valor menor de pedidos, porém um valor medio maior.

No caso são apresenta o maior número de pedidos e é o estado que mais vende, mesmo sendo o estado com menor valor médio por pedido, o que nos faz pensar que mesmo que são paulo tenha um valor médio abaixo de todos os outros estados, temos muito mais pessoas comprando por lá, o que gera campanhas de marketing melhores e com mais retornos, por haver mais pessoas para receber.

Enquanto nas regiões Norte e Nordeste, podemos investir em melhorar o marketing e produtos característico de compras dessas pessoas, pois se o valor medio de compras deles é maior, temos uma têndencia que pessoas que compram dessa região vão gastar mais, assim podemos investir no marketing e promoções para trazer mais pessoas dessas regiões para comprar em nosso e-commerce.

3. Solução de Problemas de Negócio (25 pontos)¶

1. Análise de Retenção: Calcule a taxa de clientes recorrentes. Considere um cliente recorrente aquele que fez mais de um pedido no período analisado. Quais insights podemos extrair destes dados?¶

In [334]:
print("VERIFICAÇÃO INICIAL:")
print(f"Total de pedidos: {len(ped)}")
print(f"Clientes únicos (customer_id): {ped['customer_id'].nunique()}")
print(f"Pedidos por cliente (média): {len(ped)/ped['customer_id'].nunique():.2f}")

# Juntar com informações de clientes únicos
merged_df = ped.merge(cli[['customer_id', 'customer_unique_id']], on='customer_id')

# Análise de retenção por cliente único
print("\n=== ANÁLISE DE RETENÇÃO ===")
ped_clientes = merged_df.groupby('customer_unique_id')['order_id'].nunique().reset_index()
ped_clientes.columns = ['customer_unique_id', 'order_count']

ped_clientes['is_recurrent'] = ped_clientes['order_count'] > 1

total_clientes = len(ped_clientes)
cliente_recorrente = ped_clientes['is_recurrent'].sum()
taxa_retencao = cliente_recorrente / total_clientes * 100

print(f"Taxa de retenção: {taxa_retencao:.2f}%")
print(f"Total de clientes únicos: {total_clientes}")
print(f"Clientes recorrentes: {cliente_recorrente}")
print(f"Clientes com apenas 1 pedido: {total_clientes - cliente_recorrente}")

# Distribuição de pedidos por cliente
print("\n=== DISTRIBUIÇÃO DE PEDIDOS ===")
distribuicao_ped = ped_clientes['order_count'].value_counts().sort_index()
print(distribuicao_ped)
VERIFICAÇÃO INICIAL:
Total de pedidos: 99281
Clientes únicos (customer_id): 99281
Pedidos por cliente (média): 1.00

=== ANÁLISE DE RETENÇÃO ===
Taxa de retenção: 3.07%
Total de clientes únicos: 95997
Clientes recorrentes: 2948
Clientes com apenas 1 pedido: 93049

=== DISTRIBUIÇÃO DE PEDIDOS ===
order_count
1     93049
2      2708
3       191
4        30
5         8
6         6
7         3
9         1
17        1
Name: count, dtype: int64

Insight problema de negócio¶

A análise acima mostra que apenas 3% dos clientes são recorrentes, enquanto 97% compram apenas uma vez, indicando uma retenção muito baixa. A média de 1 pedido por cliente confirma a dependência de novos clientes, dado que nossos próprios clientes só compram geralmente uma vez, então precisamos sempre atrair novos clientes. Esses dados apontam para um problema crítico de fidelização, sugerindo a necessidade de estratégias como programas de fidelidade ou pontos, remarketing e melhorias na experiência do cliente pós venda para reforçar a experiência de boa compra e fazer os mesmo clientes comprarem novamente.

2. Predição de Atraso: Crie um modelo simples para prever se um pedido será entregue com atraso.¶

Vou quebrar esse problema em alguns blocos de código¶

Definir o atraso¶

Para definir o atraso terei que comparar as colunas delivered_customer_date com estimated_delivery_date. Na Limpeza dos dados eu optei por não excluir os dados, porém como estamos tratando com um modelo de predição, ter dados nulos seria razoavelmente impactante, assim vou tratar esses dados nulos em um novo dataset, simplesmente excluindo eles, no geral os dados só representam cerca de 3% do conjunto de dados, então não será um problema tão grande fazer isso.

In [335]:
df_previsao = dataframes['olist_orders_dataset']

print(df_previsao.isnull().sum())
order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                   0
order_delivered_carrier_date     1637
order_delivered_customer_date    2819
order_estimated_delivery_date       0
enviados                            0
entregues                           0
mes                                 0
dtype: int64
In [336]:
df_previsao = df_previsao.dropna(subset=['order_delivered_carrier_date', 'order_delivered_customer_date'])
In [337]:
df_previsao.isnull().sum()
Out[337]:
order_id                         0
customer_id                      0
order_status                     0
order_purchase_timestamp         0
order_approved_at                0
order_delivered_carrier_date     0
order_delivered_customer_date    0
order_estimated_delivery_date    0
enviados                         0
entregues                        0
mes                              0
dtype: int64

Agora podemos começar a definir os atrasos, dado que não temos mais dados nulos para serem comparados.

In [338]:
df_previsao['Atrasado'] = (df_previsao['order_delivered_carrier_date'] > df_previsao['order_estimated_delivery_date']).astype(int)
/tmp/ipykernel_5750/4015680055.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

In [339]:
print(df_previsao['Atrasado'].value_counts())
print(f'\nProporção:\n{df_previsao['Atrasado'].value_counts(normalize=True)}')
Atrasado
0    95996
1      465
Name: count, dtype: int64

Proporção:
Atrasado
0    0.995179
1    0.004821
Name: proportion, dtype: float64

No nosso dataset, temos que 465 produtos foram entregados com atraso, enquanto 95996 foram entregados no prazo. Uma proporção de 99.52% para 0.48%

Features do modelo¶

In [340]:
df_previsao['tempo_entrega'] = (df_previsao['order_delivered_customer_date'] - df_previsao['order_purchase_timestamp']).dt.days
df_previsao['tempos_estimado'] = (df_previsao['order_estimated_delivery_date'] - df_previsao['order_purchase_timestamp']).dt.days
/tmp/ipykernel_5750/4036768297.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

/tmp/ipykernel_5750/4036768297.py:2: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

Vou usar a estimativa_rapida como 7, pois foi o tempo com mais pedidos na analise exploratoria

In [341]:
df_previsao['estimativa_rapida'] = (df_previsao['tempos_estimado'] < 7).astype(int) 
/tmp/ipykernel_5750/545958902.py:1: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

vou fazer um merge nos datasets para poder ter a categoria de produtos no df_previsao

In [342]:
df_previsao = df_previsao.merge(items[['order_id', 'product_id']], on='order_id', how='left')
df_previsao = df_previsao.merge(prod[['product_id', 'product_category_name']], on='product_id', how='left')
In [343]:
# Todas as colunas do dataset de previsao
df_previsao.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 110180 entries, 0 to 110179
Data columns (total 17 columns):
 #   Column                         Non-Null Count   Dtype         
---  ------                         --------------   -----         
 0   order_id                       110180 non-null  object        
 1   customer_id                    110180 non-null  object        
 2   order_status                   110180 non-null  object        
 3   order_purchase_timestamp       110180 non-null  datetime64[ns]
 4   order_approved_at              110180 non-null  datetime64[ns]
 5   order_delivered_carrier_date   110180 non-null  datetime64[ns]
 6   order_delivered_customer_date  110180 non-null  datetime64[ns]
 7   order_estimated_delivery_date  110180 non-null  datetime64[ns]
 8   enviados                       110180 non-null  int64         
 9   entregues                      110180 non-null  int64         
 10  mes                            110180 non-null  int32         
 11  Atrasado                       110180 non-null  int64         
 12  tempo_entrega                  110180 non-null  int64         
 13  tempos_estimado                110180 non-null  int64         
 14  estimativa_rapida              110180 non-null  int64         
 15  product_id                     110180 non-null  object        
 16  product_category_name          108643 non-null  object        
dtypes: datetime64[ns](5), int32(1), int64(6), object(5)
memory usage: 13.9+ MB

Preparação dos Dados¶

In [344]:
df_previsao.isnull().sum()
Out[344]:
order_id                            0
customer_id                         0
order_status                        0
order_purchase_timestamp            0
order_approved_at                   0
order_delivered_carrier_date        0
order_delivered_customer_date       0
order_estimated_delivery_date       0
enviados                            0
entregues                           0
mes                                 0
Atrasado                            0
tempo_entrega                       0
tempos_estimado                     0
estimativa_rapida                   0
product_id                          0
product_category_name            1537
dtype: int64

Temos algmas categorias de produtos com nomes nulos,vou substituir por uma flag.

In [345]:
df_previsao['product_category_name'] = df_previsao['product_category_name'].fillna('desconhecido')
In [346]:
df_previsao.isnull().sum()
Out[346]:
order_id                         0
customer_id                      0
order_status                     0
order_purchase_timestamp         0
order_approved_at                0
order_delivered_carrier_date     0
order_delivered_customer_date    0
order_estimated_delivery_date    0
enviados                         0
entregues                        0
mes                              0
Atrasado                         0
tempo_entrega                    0
tempos_estimado                  0
estimativa_rapida                0
product_id                       0
product_category_name            0
dtype: int64
In [347]:
# Enconding pela categoria do produto
le = LabelEncoder()
df_previsao['product_category_encoded'] = le.fit_transform(df_previsao['product_category_name']) 
In [348]:
# ultimas features
features = ['tempo_entrega', 'tempos_estimado', 'estimativa_rapida', 'product_category_encoded']
X = df_previsao[features]
y = df_previsao['Atrasado']

# Divisão treino/teste
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42, stratify=y)

Vou treinar o Modelo¶

In [349]:
model = RandomForestClassifier(random_state=42)
model.fit(X_train, y_train)

# Previsoes
y_pred = model.predict(X_test)
In [350]:
print("Acuracia:", accuracy_score(y_test, y_pred))
print("Precisao:", precision_score(y_test, y_pred))
print("Recall:", recall_score(y_test, y_pred))
print("F1 Score:", f1_score(y_test, y_pred))
Acuracia: 0.994826647304411
Precisao: 0.44776119402985076
Recall: 0.2803738317757009
F1 Score: 0.3448275862068966

Resultados¶

  1. A acuracia do modelo esta alta, dizendo que o modelo acertou 99.48% das previsões
  2. O modelo apresenta uma precisão não tão alta, pois quando o modelo prevê que um pedido vai atrasar ele acerta 45% da vezes.
  3. O modelo conseguiu identificar apenas 28% dos pedidos que de fato atrasaram, o que acaba sendo preocupante caso tivessemos um numero grande de atrasos.
  4. O F1 score, esta realmente muito abaixo do esperado para um modelo de previsão, refletindo a dificuldade do modelo em lidar com os pedidos em atraso.

O que podemos tirar desse modelo é que, temos dados majoritariamente entregues no prazo ideal, literalmente mais de 99% dos pedidos foram entregues sem atraso, os pedidos que foram entregues fora do prazo podem até ser considerados outliers como vou demonstar no boxplot abaixo, dito isso, o modelo não é tão robusto para fazer a previsão dos atrasados, porém a parcela desses dados é bem baixa.

In [351]:
sns.boxplot(data=df_previsao, x='Atrasado', y='tempo_entrega')
plt.title('Tempo de Entrega por Status de Atraso')
plt.show()
No description has been provided for this image

3.3 Segmentação de Clientes: Utilize técnicas de clustering para segmentar os clientes em grupos. Analise o comportamento de cada grupo e sugira estratégias de marketing específicas para cada um.¶

Preparação e agregação dos dados¶

In [352]:
#clustering
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
sns.set(style="whitegrid")
In [353]:
# Merge pedido e cliente
df3 = pd.merge(ped, cli, on='customer_id')

# df3 com items
df3 = pd.merge(df3, items, on='order_id')

# df3 com pagamentos
df3 = pd.merge(df3, pag, on='order_id')

# df3 com reviews
df3 = pd.merge(df3, reviews[['order_id', 'review_score']], on='order_id', how='left')
In [354]:
# Criacao de coluna
df3['tempo_entrega'] = (df3['order_delivered_customer_date'] - df3['order_purchase_timestamp']).dt.days
In [355]:
df3.head(1)
Out[355]:
order_id customer_id order_status order_purchase_timestamp order_approved_at order_delivered_carrier_date order_delivered_customer_date order_estimated_delivery_date enviados entregues ... seller_id shipping_limit_date price freight_value payment_sequential payment_type payment_installments payment_value review_score tempo_entrega
0 e481f51cbdc54678b7cc49136f2d6af7 9ef432eb6251297304e76186b10a928d delivered 2017-10-02 10:56:33 2017-10-02 11:07:15 2017-10-04 19:55:00 2017-10-10 21:25:13 2017-10-18 1 1 ... 3504c0cb71d7fa48d967e0e4c94d59d9 2017-10-06 11:07:15 29.99 8.72 1 credit_card 1 18.12 4.0 8.0

1 rows × 27 columns

Criação de Features¶

In [356]:
df_clientes = df3.groupby('customer_id').agg({
    'order_id': 'nunique',                                
    'payment_value': 'sum',
    'price': 'mean',                                      
    'review_score': 'mean',                               
    'tempo_entrega': 'mean'                              
}).reset_index()

df_clientes.columns = ['customer_id', 'qtd_pedidos', 'valor_total', 'ticket_medio', 'avaliacao_media', 'tempo_medio_entrega']

Pré-processamento dos dados¶

In [357]:
print(df_clientes.info())
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 98651 entries, 0 to 98650
Data columns (total 6 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   customer_id          98651 non-null  object 
 1   qtd_pedidos          98651 non-null  int64  
 2   valor_total          98651 non-null  float64
 3   ticket_medio         98651 non-null  float64
 4   avaliacao_media      97902 non-null  float64
 5   tempo_medio_entrega  96461 non-null  float64
dtypes: float64(4), int64(1), object(1)
memory usage: 4.5+ MB
None
In [358]:
print(df_clientes.isnull().sum())
customer_id               0
qtd_pedidos               0
valor_total               0
ticket_medio              0
avaliacao_media         749
tempo_medio_entrega    2190
dtype: int64

Temos poucos dados nulos, por isso vou fazer e exclusão direta deles.

In [359]:
df_clientes = df_clientes.dropna()
In [360]:
# Normalizacao dos dados
features = ['qtd_pedidos', 'valor_total', 'ticket_medio', 'avaliacao_media', 'tempo_medio_entrega']
scaler = StandardScaler()
X_scaled = scaler.fit_transform(df_clientes[features])

Elbow Method¶

In [361]:
inertia = []
for k in range(1, 11):
    kmeans = KMeans(n_clusters=k, random_state=42)
    kmeans.fit(X_scaled)
    inertia.append(kmeans.inertia_)

plt.plot(range(1, 11), inertia, marker='o')
plt.xlabel('Número de Clusters')
plt.ylabel('Inércia')
plt.title('Método do Cotovelo')
plt.show()
No description has been provided for this image
In [362]:
kmeans = KMeans(n_clusters=4, random_state=42)
df_clientes['cluster'] = kmeans.fit_predict(X_scaled)
In [363]:
cluster_summary = df_clientes.groupby('cluster')[features].mean().round(2)
display(cluster_summary)
qtd_pedidos valor_total ticket_medio avaliacao_media tempo_medio_entrega
cluster
0 1.0 149.15 97.75 4.66 7.59
1 1.0 234.54 108.78 1.24 19.73
2 1.0 1822.02 1016.29 4.16 13.08
3 1.0 182.92 120.58 4.36 20.26

Insights¶

Cluster 0: Cliente Satisfeito com Baixo Gasto

  1. Alta avaliação (4.66)
  2. Baixo gasto e ticket médio (149,15 total / 97,75 por pedido)
  3. Entrega rápida (7,59 dias)

Possível cliente fiel e satisfeito. Pode ser cultivado com programas de fidelidade


Cluster 1: Cliente Insatisfeito

  1. Baixíssima avaliação (1.24)
  2. Entrega muito demorada (19,73 dias)
  3. Gasto médio (234,54), mas provavelmente frustrado

Cliente com risco de abandono. Requer foco em melhorar logística e suporte.


Cluster 2: Cliente Premium

  1. Gasto total e ticket altíssimos (1822 / 1016!)
  2. Boa avaliação (4.16)
  3. Entrega mediana (13 dias)

Cliente de alto valor. Estratégia: ofertas exclusivas, atendimento personalizado.


Cluster 3: Cliente Potencial Insatisfeito
Ticket médio mais alto que o cluster 1, mas:

  1. Tempo de entrega ainda pior (20,26 dias)
  2. Avaliação boa (4.36)

Cliente tolerante, mas risco se continuar com atraso. Otimizar entrega.

--

Estategia C0: Recompensas por lealdade, descontos por frequência.

Estategia C1: Recuperação com cupons, créditos e descontos, em conjunto com uma logistica mais rápida para região.

Estategia C2: Benefícios únicos por alto gasto na plataforma, algum tipo de acesso que só clientes com esse tipo de gasto podem obter, assim fazemos o cliente se sentir pertencente há um nicho e outros clientes querendo fazer parte do mesmo padrão.

Estategia C3: Melhorar o prazo de entrega e oferecer frete grátis para os clientes em questão, além disso notificar os status das compras constantemente quando o cliente efetuar a aquisição.

4. Análise de Satisfação: Explore a relação entre a nota de avaliação dos clientes e diferentes aspectos como categoria do produto, tempo de entrega, valor do pedido, etc. Identifique fatores que mais impactam na satisfação do cliente.¶

Preparação dos Dados¶

In [364]:
# Vou fazer um merge dos dados novamente, utilizando os dataset anteriomente definidos
df4 = pd.merge(reviews, ped, on='order_id', how='left')
df4 = pd.merge(df4, pag, on='order_id', how='left')
df4 = pd.merge(df4, items, on='order_id', how='left')
df4 = pd.merge(df4, prod, on='product_id', how='left')
df4 = pd.merge(df4, ctg_nome, on='product_category_name', how='left')
df4 = pd.merge(df4, vend, on='seller_id', how='left')
In [365]:
# Criacao de colunas para tempos de entrega
df4['tempo_entrega'] = (df4['order_delivered_customer_date'] - df4['order_purchase_timestamp']).dt.days
df4['estimativa_entrega'] = (df4['order_estimated_delivery_date'] - df4['order_purchase_timestamp']).dt.days
df4['atraso_entrega'] = df4['tempo_entrega'] - df4['estimativa_entrega']
In [366]:
# VAlor total preco e frete
df4['valor_total_pedido'] = df4['price'] + df4['freight_value']

Análise por Categoria de produto¶

In [367]:
# Avaliação média por categoria de produto (em inglês)
review_categoria = df4.groupby('product_category_name_english')['review_score'].mean().sort_values(ascending=False)
In [368]:
# Top 10 melhores categorias
review_categoria.head(10)
Out[368]:
product_category_name_english
cds_dvds_musicals                        4.642857
fashion_childrens_clothes                4.500000
books_general_interest                   4.438503
books_imported                           4.419355
flowers                                  4.419355
costruction_tools_tools                  4.415842
books_technical                          4.375465
food_drink                               4.324138
small_appliances_home_oven_and_coffee    4.320513
luggage_accessories                      4.295945
Name: review_score, dtype: float64
In [369]:
# Top 10 piores categorias
review_categoria.tail(10)
Out[369]:
product_category_name_english
furniture_bedroom                    3.841270
audio                                3.840849
furniture_mattress_and_upholstery    3.804878
fashio_female_clothing               3.780000
fixed_telephony                      3.672862
home_comfort_2                       3.642857
fashion_male_clothing                3.548611
office_furniture                     3.526791
diapers_and_hygiene                  3.256410
security_and_services                2.500000
Name: review_score, dtype: float64
In [370]:
plt.figure(figsize=(22, 20))
review_categoria.sort_values().plot(kind='barh')
plt.title('Avaliação Média por Categoria de Produto')
plt.xlabel('Avaliação Média')
plt.ylabel('Categoria de Produto')
plt.show()
No description has been provided for this image

Insights¶

Dvds, roupas de criancas e livros tem a maior Avalição média. O que faz sentido tem em vista, que as pessoas que compram geralmente apresentam um gosto afirmativo pelo produto antes da compra. Diferete de serviçõs de seguraça que normalmente você compra para se precaver de algo, e o sistema falha ou vem com defeito, ou é de dificil uso, o que faz a avaliação ser pior

Análise por Tempo de Entrega¶

In [371]:
# Correlações
print("Correlação entre tempo de entrega e avaliação:", 
      df4['tempo_entrega'].corr(df4['review_score']))
print("Correlação entre atraso na entrega e avaliação:", 
      df4['atraso_entrega'].corr(df4['review_score']))

# Avaliação por faixa de tempo de entrega
df4['faixa_tempo_entrega'] = pd.cut(df4['tempo_entrega'], 
                                   bins=[0, 5, 10, 15, 20, 30, 50, 100],
                                   labels=['0-5', '6-10', '11-15', '16-20', '21-30', '31-50', '50+'])

avaliacao_por_tempo = df4.groupby('faixa_tempo_entrega')['review_score'].mean()

plt.figure(figsize=(10, 5))
sns.lineplot(x=avaliacao_por_tempo.index, y=avaliacao_por_tempo.values)
plt.title('Avaliação Média por Tempo de Entrega (dias)')
plt.xlabel('Tempo de Entrega (dias)')
plt.ylabel('Avaliação Média')
plt.show()
Correlação entre tempo de entrega e avaliação: -0.3027567826448285
Correlação entre atraso na entrega e avaliação: -0.22847841222502543
/tmp/ipykernel_5750/411135487.py:12: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

No description has been provided for this image

Insight¶

Aqui temos uma relação que faz todo o sentido, quanto maior o tempo de entrega, menor a avaliação dos clientes, o que faz total sentido, já que ninguem gosta de pedidos atrasados.

Análise por Valor do Pedido¶

In [372]:
# Correlação
print("Correlação entre valor total do pedido e avaliação:", 
      df4['valor_total_pedido'].corr(df4['review_score']))

# Avaliação por faixa de valor
df4['faixa_valor_pedido'] = pd.cut(df4['valor_total_pedido'], 
                                  bins=[0, 50, 100, 150, 200, 300, 500, 1000, 5000],
                                  labels=['0-50', '51-100', '101-150', '151-200', '201-300', '301-500', '501-1000', '1000+'])

avaliacao_por_valor = df4.groupby('faixa_valor_pedido')['review_score'].mean()

plt.figure(figsize=(10, 5))
sns.barplot(x=avaliacao_por_valor.index, y=avaliacao_por_valor.values)
plt.title('Avaliação Média por Faixa de Valor do Pedido')
plt.xlabel('Valor do Pedido (R$)')
plt.ylabel('Avaliação Média')
plt.xticks(rotation=45)
plt.show()
Correlação entre valor total do pedido e avaliação: -0.007406649176182689
/tmp/ipykernel_5750/2851571662.py:10: FutureWarning:

The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

No description has been provided for this image

Insight do gráfico¶

O valor do pedido não impacta diretamente na avaliação, dessa forma temos que independente do valor, estamos olhando para metódos que não impactam no preço

4. Análise por Método de Pagamento¶

In [373]:
# Avaliação por tipo de pagamento
avaliacao_por_pagamento = df4.groupby('payment_type')['review_score'].mean().sort_values(ascending=False)

plt.figure(figsize=(8, 5))
sns.barplot(x=avaliacao_por_pagamento.index, y=avaliacao_por_pagamento.values)
plt.title('Avaliação Média por Método de Pagamento')
plt.xlabel('Método de Pagamento')
plt.ylabel('Avaliação Média')
plt.show()
No description has been provided for this image

Insight do gráfico¶

Temos que no geral o método de pagamento não determina diretamente na avaliação, porém temos um método não definido que devemos solicitar para o responsável pela coleta dos dados o que ele quer dizer, dado que é o unico método de pagamento que tem avaliação diferente da média

5. Análise por Estado do Vendedor¶

In [374]:
avaliacao_por_estado = df4.groupby('seller_state')['review_score'].mean().sort_values(ascending=False)

plt.figure(figsize=(12, 6))
sns.barplot(x=avaliacao_por_estado.index, y=avaliacao_por_estado.values)
plt.title('Avaliação Média por Estado do Vendedor')
plt.xlabel('Estado do Vendedor')
plt.ylabel('Avaliação Média')
plt.show()
No description has been provided for this image

Insigths¶

Temos uma avaliação média parecida par quase todo os vendedores, porém os vendedores do AC e AM, tem uma média significantemente melhor, pertencendo há uma região na qual as pessoas tendem a ter um valor de gasto médio maior, sendo assim, podemos dizer indiretamente, que a régua de satisfação dessas pessoas pelo vendedor tende a diminui conforme o gasto mais elevado.

6. Análise Multivariada (Fatores mais Importantes)¶

In [375]:
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_squared_error

# Preparar dados para o modelo
modelo_df = df4[['review_score', 'tempo_entrega', 'atraso_entrega', 
                'valor_total_pedido', 'payment_installments', 'product_weight_g',
                'product_photos_qty', 'seller_state']].dropna()

# Converter variáveis categóricas
modelo_df = pd.get_dummies(modelo_df, columns=['seller_state'], drop_first=True)

# Separar variáveis
X = modelo_df.drop('review_score', axis=1)
y = modelo_df['review_score']

# Dividir em treino e teste
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Treinar modelo
modelo = RandomForestRegressor(n_estimators=100, random_state=42)
modelo.fit(X_train, y_train)

# Avaliar modelo
y_pred = modelo.predict(X_test)
print("RMSE:", np.sqrt(mean_squared_error(y_test, y_pred)))

# Importância das variáveis
importancia = pd.DataFrame({
    'variavel': X.columns,
    'importancia': modelo.feature_importances_
}).sort_values('importancia', ascending=False)

plt.figure(figsize=(10, 6))
sns.barplot(x='importancia', y='variavel', data=importancia)
plt.title('Importância dos Fatores na Avaliação do Cliente')
plt.xlabel('Importância')
plt.ylabel('Fator')
plt.show()
RMSE: 1.1078294292935758
No description has been provided for this image

Insights¶

Temos que o principal motivo na avaliação do cliente é o atraso na entrega, que também se relaciona com o valor_total do pedido, já que quanto mais longe, maior o frete e maior o valor do pedido, que sendo entregue com atraso prejudica diretamente a avaliação.

4. Dashboard e visualizaçòes¶

Vou fazer as visualizações e dashboards aqui no notebook, porque esta dito no github do case que todos os insights devem estar no notebook, porém caso eu pudesse redirecionar para outra página eu faria um dashboard com streamlit para rodar no server deles, porém como não sei se isso é permitido farei meu melhor com plotly.

1. Um dashboard geral que mostre a evolução das vendas ao longo do tempo, com filtros por estado e categoria de produto¶

Fiz o Projeto diretamente no vscode, porém não consegui fazer uma forma de executar o notebook no meu jupyter notebook web, só consegui a reprodução dos dashboard pelo jupyter lab.

In [376]:
import plotly.express as px
from ipywidgets import interact, widgets, Layout
from IPython.display import display, HTML
from plotly.offline import init_notebook_mode
import plotly.express as px
import plotly.graph_objects as go


init_notebook_mode(connected=True)

#  Preparação dos dados 
orders_with_items = pd.merge(
    dataframes['olist_orders_dataset'],
    dataframes['olist_order_items_dataset'],
    on='order_id',
    how='inner'
)

orders_with_products = pd.merge(
    orders_with_items,
    dataframes['olist_products_dataset'],
    on='product_id',
    how='left'
)

orders_with_products = pd.merge(
    orders_with_products,
    dataframes['product_category_name_translation'],
    on='product_category_name',
    how='left'
)

orders_with_customers = pd.merge(
    orders_with_products,
    dataframes['olist_customers_dataset'][['customer_id', 'customer_state']],
    on='customer_id',
    how='left'
)

orders_with_sellers = pd.merge(
    orders_with_customers,
    dataframes['olist_sellers_dataset'][['seller_id', 'seller_state']],
    on='seller_id',
    how='left'
)

orders_with_sellers['order_purchase_timestamp'] = pd.to_datetime(orders_with_sellers['order_purchase_timestamp'])
orders_with_sellers['order_date'] = orders_with_sellers['order_purchase_timestamp'].dt.date
orders_with_sellers['order_month'] = orders_with_sellers['order_purchase_timestamp'].dt.to_period('M').astype(str)
orders_with_sellers['product_category_name_english'] = orders_with_sellers['product_category_name_english'].fillna('Unknown')

# 2. Criar controles interativos com layout ajustado
state_dropdown = widgets.Dropdown(
    options=['Todos'] + sorted(orders_with_sellers['customer_state'].unique().tolist()),
    value='Todos',
    description='Estado:',
    layout=Layout(width='50%')
)

category_dropdown = widgets.Dropdown(
    options=['Todos'] + sorted(orders_with_sellers['product_category_name_english'].dropna().unique().tolist()),
    value='Todos',
    description='Categoria:',
    layout=Layout(width='50%')
)

time_period = widgets.RadioButtons(
    options=['Diário', 'Mensal'],
    value='Mensal',
    description='Período:',
    layout=Layout(width='50%')
)

# Container para os controles
controls = widgets.VBox([state_dropdown, category_dropdown, time_period],
                      layout=Layout(border='1px solid gray', padding='10px'))

# Área para o gráfico
output = widgets.Output()

# Função de atualização
def update_plot(customer_state='Todos', product_category='Todos', time_period='Mensal'):
    with output:
        output.clear_output(wait=True)
        
        # Filtragem dos dados
        filtered = orders_with_sellers.copy()
        
        if customer_state != 'Todos':
            filtered = filtered[filtered['customer_state'] == customer_state]
        
        if product_category != 'Todos':
            filtered = filtered[filtered['product_category_name_english'] == product_category]
        
        time_col = 'order_date' if time_period == 'Diário' else 'order_month'
        
        sales_data = filtered.groupby(time_col).agg(
            total_sales=('price', 'sum'),
            order_count=('order_id', 'nunique'),
            avg_order_value=('price', 'mean')
        ).reset_index()
        
        # Criar grafico
        fig = px.line(
            sales_data,
            x=time_col,
            y='total_sales',
            title=f'Evolução das Vendas {"por Dia" if time_period == "Diário" else "por Mês"}',
            labels={
                'total_sales': 'Valor Total de Vendas (R$)',
                time_col: 'Data' if time_period == 'Diário' else 'Mês'
            }
        )
        
        fig.update_layout(
            height=500,
            xaxis_title='Período',
            yaxis_title='Valor Total de Vendas (R$)',
            hovermode='x unified'
        )
        
        # Mostrar gráfico
        display(fig)
        
        # Metricas
        metrics_html = f"""
        <div style="margin:10px; padding:10px; border:1px solid #ddd; border-radius:5px;">
            <h4>Métricas:</h4>
            <p><b>Total de Vendas:</b> R$ {sales_data['total_sales'].sum():,.2f}</p>
            <p><b>Número de Pedidos:</b> {sales_data['order_count'].sum():,}</p>
            <p><b>Valor Médio por Pedido:</b> R$ {sales_data['avg_order_value'].mean():,.2f}</p>
        </div>
        """
        display(HTML(metrics_html))

# Configurar interatividade
def on_change(change):
    update_plot(
        customer_state=state_dropdown.value,
        product_category=category_dropdown.value,
        time_period=time_period.value
    )

state_dropdown.observe(on_change, names='value')
category_dropdown.observe(on_change, names='value')
time_period.observe(on_change, names='value')

# Exibir o dashboard
display(HTML("<h2>Dashboard de Evolução de Vendas</h2>"))
display(controls)
display(output)

# Atualizar com dados iniciais
update_plot()

Dashboard de Evolução de Vendas

VBox(children=(Dropdown(description='Estado:', layout=Layout(width='50%'), options=('Todos', 'AC', 'AL', 'AM',…
Output()

Insights¶

O dashboard é bem auto-explicativo e interativo, podendo ser observado e filtrado a evolução das vendas com o tempo

2. Um mapa de calor mostrando a concentração de vendas por região/estado do Brasil¶

In [377]:
import requests

# GeoJSON dos estados do Brasil
url = 'https://raw.githubusercontent.com/codeforamerica/click_that_hood/master/public/data/brazil-states.geojson'
geojson_states = requests.get(url).json()

# Mapeamento de siglas para nomes completos
uf_map = {
    'AC': 'Acre', 'AL': 'Alagoas', 'AP': 'Amapá', 'AM': 'Amazonas',
    'BA': 'Bahia', 'CE': 'Ceará', 'DF': 'Distrito Federal', 'ES': 'Espírito Santo',
    'GO': 'Goiás', 'MA': 'Maranhão', 'MT': 'Mato Grosso', 'MS': 'Mato Grosso do Sul',
    'MG': 'Minas Gerais', 'PA': 'Pará', 'PB': 'Paraíba', 'PR': 'Paraná',
    'PE': 'Pernambuco', 'PI': 'Piauí', 'RJ': 'Rio de Janeiro', 'RN': 'Rio Grande do Norte',
    'RS': 'Rio Grande do Sul', 'RO': 'Rondônia', 'RR': 'Roraima', 'SC': 'Santa Catarina',
    'SP': 'São Paulo', 'SE': 'Sergipe', 'TO': 'Tocantins'
}

# Preparar dados
ped_cli = ped.merge(cli, on='customer_id')
orders_by_state = ped_cli['customer_state'].value_counts().reset_index()
orders_by_state.columns = ['uf', 'num_orders']
orders_by_state['state_name'] = orders_by_state['uf'].map(uf_map)

# Gerar mapa 
fig = px.choropleth(
    orders_by_state,
    geojson=geojson_states,
    locations='state_name',
    featureidkey='properties.name',
    color='num_orders',
    color_continuous_scale='Blues',
    labels={'num_orders': 'Número de Pedidos'},
)

# visualização
fig.update_geos(
    fitbounds="locations",
    visible=False,
    showcountries=False,
    showcoastlines=False,
    showland=True,
    landcolor='white'
)

fig.update_layout(
    title_text='📦 Concentração de Pedidos por Estado no Brasil',
    title_x=0.5,
    margin={"r":0,"t":50,"l":0,"b":0},
    height=600,
    width=800,
    coloraxis_colorbar=dict(
        title="Pedidos",
        ticks="outside"
    )
)

fig.show()
In [378]:
# Juntar pedidos com clientes
ped_cli = ped.merge(cli, on='customer_id')

# Calcular valor total por pedido
total_ped = items.groupby('order_id')['price'].sum().reset_index()

# Juntar com estado do cliente
total_ped_state = ped_cli.merge(total_ped, on='order_id')

# 2. Calcular valor médio gasto por estado
avg_spend_by_state = total_ped_state.groupby('customer_state')['price'].mean().reset_index()
avg_spend_by_state.columns = ['uf', 'avg_spent']
avg_spend_by_state['state_name'] = avg_spend_by_state['uf'].map(uf_map)

# 3. Criar o gráfico
fig = px.choropleth(
    avg_spend_by_state,
    geojson=geojson_states,
    locations='state_name',
    featureidkey='properties.name',
    color='avg_spent',
    color_continuous_scale='Oranges',
    labels={'avg_spent': 'Ticket Médio (R$)'},
)

# 4. Ajustes visuais
fig.update_geos(
    fitbounds="locations",
    visible=False,
    showcountries=False,
    showcoastlines=False,
    showland=True,
    landcolor='white'
)

fig.update_layout(
    title_text='🛒 Valor Médio Gasto por Pedido em Cada Estado',
    title_x=0.5,
    margin={"r":0,"t":50,"l":0,"b":0},
    height=600,
    width=800,
    coloraxis_colorbar=dict(
        title="R$ por pedido",
        ticks="outside"
    )
)

fig.show()

Insights¶

Como observado na Análise exploratória, o Valor médio gasto é maior no norte e nordeste, porém a concentração de compras é maior no sul, principalmente em são paulo.

3. Um conjunto de gráficos que apresente a relação entre avaliação do cliente e tempo de entrega¶

In [379]:
# Carregar as tabelas necessárias
reviews = dataframes['olist_order_reviews_dataset']
orders = dataframes['olist_orders_dataset']

# Juntar as tabelas pela chave order_id
merged = pd.merge(reviews, orders, on='order_id', how='inner')

# Converter colunas de data
merged['order_purchase_timestamp'] = pd.to_datetime(merged['order_purchase_timestamp'])
merged['order_delivered_customer_date'] = pd.to_datetime(merged['order_delivered_customer_date'])

# Calcular o tempo de entrega (em dias)
merged['delivery_time'] = (merged['order_delivered_customer_date'] - merged['order_purchase_timestamp']).dt.days

# Remover linhas com delivery_time nulo
merged = merged.dropna(subset=['delivery_time'])
In [380]:
# boxplot
plt.figure(figsize=(10, 6))
sns.boxplot(data=merged, x='review_score', y='delivery_time')
plt.title('Tempo de entrega por nota de avaliação')
plt.xlabel('Nota do cliente')
plt.ylabel('Tempo de entrega (dias)')
plt.grid(True)
plt.show()
No description has been provided for this image
In [381]:
# grafico de barras
avg_delivery = merged.groupby('review_score')['delivery_time'].mean().reset_index()
plt.figure(figsize=(8, 5))
sns.barplot(data=avg_delivery, x='review_score', y='delivery_time', color=sns.color_palette('PuBu')[4])
plt.title('Média do tempo de entrega por nota de avaliação')
plt.xlabel('Nota do cliente')
plt.ylabel('Tempo médio de entrega (dias)')
plt.grid(True)
plt.show()
No description has been provided for this image

Insights¶

Como ja observado anteriormente, quanto maior o tempo de entrega, menor a nota do cliente.

4. Um dashboard de análise dos vendedores, mostrando quais têm melhor desempenho em termos de volume de vendas, satisfação do cliente e tempo de entrega¶

In [382]:
# Converter datas
ped['order_purchase_timestamp'] = pd.to_datetime(ped['order_purchase_timestamp'])
ped['order_delivered_customer_date'] = pd.to_datetime(ped['order_delivered_customer_date'])

# Tempo de entrega por pedido
ped['delivery_time'] = (ped['order_delivered_customer_date'] - ped['order_purchase_timestamp']).dt.days

#Juntar reviews com pedidos
review_ped = pd.merge(reviews[['order_id', 'review_score']], ped[['order_id', 'delivery_time']], on='order_id')

# Juntar com os itens
base = pd.merge(review_ped, items[['order_id', 'seller_id']], on='order_id')
In [383]:
# Agrupar por vendedor
metricas_vend = base.groupby('seller_id').agg(
    volume_vendas=('order_id', 'count'),
    nota_media=('review_score', 'mean'),
    entrega_media=('delivery_time', 'mean')
).reset_index()

# Opcional: filtrar vendedores com pelo menos 20 vendas
metricas_vend = metricas_vend[metricas_vend['volume_vendas'] >= 20]
In [384]:
top_volume = metricas_vend.sort_values(by='volume_vendas', ascending=False).head(10)

sns.barplot(data=top_volume, x='volume_vendas', y='seller_id', palette='Blues_r')
plt.title('Top 10 vendedores por volume de vendas')
plt.xlabel('Número de vendas')
plt.ylabel('ID do vendedor')
plt.show()
/tmp/ipykernel_5750/2636866980.py:3: FutureWarning:



Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.


No description has been provided for this image
In [385]:
top_nota = metricas_vend.sort_values(by='nota_media', ascending=False).head(10)

sns.barplot(data=top_nota, x='nota_media', y='seller_id', palette='Greens_r')
plt.title('Top 10 vendedores por avaliação média')
plt.xlabel('Nota média')
plt.ylabel('ID do vendedor')
plt.show()
/tmp/ipykernel_5750/1127399185.py:3: FutureWarning:



Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.


No description has been provided for this image
In [386]:
top_entrega = metricas_vend.sort_values(by='entrega_media', ascending=True).head(10)

sns.barplot(data=top_entrega, x='entrega_media', y='seller_id', palette='Oranges_r')
plt.title('Top 10 vendedores com menor tempo de entrega')
plt.xlabel('Tempo médio de entrega (dias)')
plt.ylabel('ID do vendedor')
plt.show()
/tmp/ipykernel_5750/1383419301.py:3: FutureWarning:



Passing `palette` without assigning `hue` is deprecated and will be removed in v0.14.0. Assign the `y` variable to `hue` and set `legend=False` for the same effect.


No description has been provided for this image
In [390]:
from IPython.display import display, Markdown

n_vendedores = metricas_vend.shape[0]
media_volume = metricas_vend['volume_vendas'].mean()
media_nota = metricas_vend['nota_media'].mean()
media_entrega = metricas_vend['entrega_media'].mean()

display(Markdown(f"""
# Análise de Desempenho dos Vendedores

**Total de vendedores analisados:** {n_vendedores}  
**Volume médio de vendas:** {media_volume:.1f}  
**Nota média dos clientes:** {media_nota:.2f}  
**Tempo médio de entrega:** {media_entrega:.2f} dias
"""))

Análise de Desempenho dos Vendedores¶

Total de vendedores analisados: 887
Volume médio de vendas: 112.6
Nota média dos clientes: 4.07
Tempo médio de entrega: 11.69 dias


In [392]:
# Ranking ponderado simples (ajustável)
metricas_vend['ranking'] = (
    metricas_vend['volume_vendas'].rank(ascending=False) +
    metricas_vend['nota_media'].rank(ascending=False) +
    metricas_vend['entrega_media'].rank(ascending=True)
)

ranking_geral = metricas_vend.sort_values(by='ranking').head(10)

display(Markdown("# Ranking Geral dos Top 10 Vendedores"))
display(ranking_geral[['seller_id', 'volume_vendas', 'nota_media', 'entrega_media']])

Ranking Geral dos Top 10 Vendedores¶

seller_id volume_vendas nota_media entrega_media
476 289cdb325fb7e7f891c38608bf9e0962 126 4.579365 6.192000
2399 c70c1b0d8ca86052f45a432a38b73958 335 4.301493 7.252252
2526 d13e50eaa47b4cbe9eb81465865d8cfc 69 4.811594 4.573529
2575 d566c37fa119d5e66c4e9052e83ee4ea 71 4.718310 5.352113
2360 c3cfdc648177fdbbbb35635a37472c53 313 4.447284 9.246006
1934 9f505651f4a6abe901a56cdc21508025 427 4.283372 7.754137
1910 9d4db00d65d7760644ac0c14edb5fd86 99 4.515152 7.673469
100 080199a181c46c657dc5aa235411be3b 82 4.609756 7.621951
1351 6edacfd9f9074789dad6d62ba7950b9c 219 4.360731 8.566820
2307 c003204e1ab016dfa150abc119207b24 201 4.422886 9.270000

Insights¶

Como só temos os ids dos vendedores, deixarei a tabela explicando a posição de cada um.